Dear friends!
What is the fastest way to import 15000000 records from Text File to SQL Server?
Currently, I am importing data using Enterprise Manager, that takes 3-4 hours for importing into the SQL table.
Thanks in advance!
+10
A:
Try bcp utility or BULK INSERT statement. BULK INSERT statement should be faster though.
Mehrdad Afshari
2009-07-26 18:15:16
+1
A:
Using SSIS there is a published benchmark that loads 2.36TB per hour. There are tricks you can do, like split the file parsing and spread the load on separate NUMA listening ports. Also the articles quotes matching the column types properly in SSIS is a big factor.
Remus Rusanu
2009-07-26 18:24:26
SSIS is great for importing data from heterogeneous and complex sources but for simple CSV text files, I doubt it can beat direct bulk insertion. I strongly suspect it uses the same stuff under the hood.
Mehrdad Afshari
2009-07-26 18:29:54
There is no pixie dust, the server has certain APIs to access and SSIS must conform to them: INSERT and BULK INSERT. But on such large tasks as 3-4 hours, if they are regular, there are tricks that can be done, specially around spiting the text parsing tasks. They are usually easier to do in SSIS.
Remus Rusanu
2009-07-26 19:05:14
+1
A:
+1 to Mehrdad's answer. Just wanted to add, if you have indexes on the table you're trying to load in to, those will affect the load speed as they have to be maintained. You may be better to remove/disable those indexes while you do the import.
AdaTheDev
2009-07-26 18:46:06