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 |
|
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 |
 |
|
|
bmcclurg64
Starting Member
6 Posts |
Posted - 2014-10-27 : 08:41:18
|
| Could you possibly explain how to utilize SSIS in my situation? |
|
|
|
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 dimensionshttp://msdn.microsoft.com/en-us/library/ms141715.aspxJaveed Ahmedhttps://www.linkedin.com/pub/javeed-ahmed/25/5b/95 |
|
|
|
bmcclurg64
Starting Member
6 Posts |
Posted - 2014-10-27 : 10:55:19
|
| Thanks, I will look into this. |
|
|
|
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/ |
|
|
|
|
|
|
Bulk Inser/Update