What is fastest way to insert many records into a SQL server database?

[Danny Smith]

Can anyone help?

I want to find the fastest way of inserting a large number of records
(50,000+) into a SQL Server database (using C#).

The records are held in memory and the options I can think of are:

1. Creating a DataSet in memory and then calling the
DataAdapter.Update(dataSet) method. This runs an Insert query for each row
and ends up being quite slow. Is there any way to insert a whole DataSet at
once?

2. Calling a stored procedure for each record that inserts a row of data.

3. Using a BULK INSERT query. I believe this involves writing out to a text
file first.

4. Creating an XML document in memory and pass it to SQL Server. Not sure
if this is possible.

Any help much appreciated!

Danny Smith

 

[Pablo Castro [MS]]

BULK INSERT is probably fastest way in most cases. However, you'll have to
write the data to disk in a location that the server process can access. The
other option is to build batches of several inserts (concatenating SQL
INSERT statements). You can also do OPENXML (option 4 listed below); here
you pay the extra cost of parsing xml on the server, but you end up with
less round-trips.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[William Ryan eMVP]

Hi Danny:

Can anyone help?

I want to find the fastest way of inserting a large number of records
(50,000+) into a SQL Server database (using C#).

The records are held in memory and the options I can think of are:

1. Creating a DataSet in memory and then calling the
DataAdapter.Update(dataSet) method. This runs an Insert query for each row
and ends up being quite slow. Is there any way to insert a whole DataSet at
once?

2. Calling a stored procedure for each record that inserts a row of data.

3. Using a BULK INSERT query. I believe this involves writing out to a text
file first.

4. Creating an XML document in memory and pass it to SQL Server. Not sure
if this is possible.

Any help much appreciated!

Danny Smith

ADO.NET is agreat data accesss technology but it's not geared toward data
transfer although it can do it. User either DTS, or Bulk INsert. You are
right, you'll need to write out the data if you are doing it from ADO.NET,
but bulk inserts will be supported in ADO.NET 2.0 for one thing and even
now, it's very fast and probably much less hassle.

You can also use DTS to automate this which is fast .... all you'll need to
do is write it out somewhere so DTS can see the dataasource. I've done this
a lot, and have to do it regularly, with record numbers more around 1/4
million records and Bulk Insert Rocks!

HTH,

Bill

 

[Danny Smith]

Thanks Pablo.

To use OPENXML do I have to output an XML file to be imported by SQL Server
(in much the same way as the BULK INSERT method) or is there a way to create
the XML file in memory and send the stream directly?

Danny

 

[Pablo Castro [MS]]

In order to use OPENXML you need to "prepare" the xml doc first in SQL
server by calling sp_xml_preparedocument. That stored-proc takes the xml
document as a string argument, so you can send that xml argument from the
client using SqlParameter.

If you look for OPENXML in the SQL Books Online index you'll find a
description and an example of using OPENXML.

Please let me know if you need further details.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.