Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2012-12-11 : 16:35:51
|
| I have requirement to load the data in respective table from the excel file.The excel file has status which states the record to added,modify,delete for a employee.Sample Excel file datastatus customerid name city ------ ------------ ------ ------Add 10000 test SFCOModify 10001 test1 NYDelete 10001 test3 DC Please let me know know best way to do it. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-11 : 17:25:42
|
The simplest way I can think of is to load the data from the Excel file into a Work Table and then write a query to Add/modify/delete. To delete the following, and similar code for Insertions and updatesDELETE y FROM YourRealTable y WHERE EXISTS( SELECT * FROM YourWorkTable w WHERE w.customerid = y.customerid AND w.status = 'Delete'); You can use SSIS to insert and to run the update queries. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-14 : 03:04:42
|
| in ssis you can use conditional task to define outputs based on status column value in dataflow task and write corresponding logic (insert/update/delete) in relevant output branches. Use OLEDB command for update and delete and then OLEDB destination for insert. If number of rows is really huge, consider doing update in update/delete branches to set bit value to indicate records to be deleted/updated and then follow up with execute sql tasks in control to do batch update and deletes. Also for insert set fast load option for OLEDB destination------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Insert rows