large amount of data insert into table

Question

I've to transfer the data from datatable to database. The total record is around 15k per time. I want to redue the time for data inserting. Should I use SqlBulk? or anything else ?

score 0 · Answer 1 · answered Feb 14 '13 at 08:41

0

If you use C# take a look on SqlBulkCopy class. There is a good article on CodeProject about how to use it with DataTable

answered Feb 14 '13 at 08:41

Andrey Gordeev

30,606
13
135
162

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

If you just aim for speed, then bulk copy might suite you well. Depending on the requirements you can also reduce logging level.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/edf280b9-2b27-48bb-a2ff-cab4b563dcb8/

How to copy a huge table data into another table in SQL Server

edited May 23 '17 at 10:32

Community

1
1

answered Feb 14 '13 at 08:46

Jocke

2,189
1
16
24

I just concern on about speed. – kst Feb 14 '13 at 08:50

score 0 · Answer 3 · answered Feb 14 '13 at 09:10

SQL bulk copy is definitively fastest with this kind of sample size as it streams the data rather than actually using regular sql commands. This gives it a slightly longer startup time but with more than ~120 rows at a time (according to my tests)

Check out my post here on how to get here if you want some stats on how sql bulk copy stacks up against a few other methods.

http://blog.staticvoid.co.nz/2012/8/17/mssql_and_large_insert_statements

enter image description here

score 0 · Answer 4 · answered Feb 14 '13 at 10:13

0

15k records is not that much really. I have a laptop with i5 CPU and 4GB ram and it inserts 15k records in several seconds. I wouldn't really bother spending too much time on finding an ideal solution.

answered Feb 14 '13 at 10:13

JdMR

1,268
14
9

large amount of data insert into table

4 Answers4