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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Bulk Inser/Update

Author  Topic 

bmcclurg64
Starting Member

6 Posts

Posted - 2014-10-27 : 07:58:14
Hello All,

Have two questions about inserting and updating bulk sets of records. Currently I use C#'s SqlBulkCopy to bulk insert records into a table. Was first off wondering if this is the best way to bulk insert records? Basically my program takes a data file from our customer and inserts that data into table. There could be 1 record there could be up to say 80,000 records at any one run to insert this data. On some of the data files where there are many thousands of records (one I am working on right now has a little over 40,000 records) it takes 30+ seconds to insert the data. Is this normal? Is there a better way to bulk insert this data?

On the other side, I later have to update these records. Basically the data file from the customer (the one I bulk insert) gets processed through some different programs and generates a new data file that I then want to take and update some of the fields in the table that I inserted the records into. Both data files will contain a primary key field. What is best way to do the bulk update? I currently created a stored procedure to take in a table-value parameter (datatable from C#) and in this SP I update the records in the table where the keys match. However, this update has been taking 7+ minutes to complete on that same 40,000 record file. Is there a better approach that would be faster to update this many records?

Doing some research found some people write to CSV and then import into temp table to then insert/update. Is this a better approach?

Any ideas or comments would be greatly appreciated.

Thank you,

Brad

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-27 : 08:02:52
it would be better to use SSIS.

Javeed Ahmed
Go to Top of Page

bmcclurg64
Starting Member

6 Posts

Posted - 2014-10-27 : 08:41:18
Could you possibly explain how to utilize SSIS in my situation?
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-27 : 10:03:23
you can load the full data into a staging table and then use the slowly changing transformation to update the changed records.

check this about slowly changing dimensions
http://msdn.microsoft.com/en-us/library/ms141715.aspx

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page

bmcclurg64
Starting Member

6 Posts

Posted - 2014-10-27 : 10:55:19
Thanks, I will look into this.
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-10-28 : 06:51:05
You may also find it useful to read the following:

http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/
Go to Top of Page
   

- Advertisement -