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

 

[Ignacio Machin \( .NET/ C# MVP \)]

I Danny,

I would go for the 3 option , but I cannot be 100% sure regarding the
others, when I did the exam for SQL-2K certification that was one of the
question and a bulk insert was the correct answer.

You better ask in the SQL server group.

Is this a one time only operation or is part of a process?
if it's a onet ime operation do what you know best.


Cheers,

 

[shrini]

The best way, i know of is bcp. check SQL books online to use that, nice
little tool

shrini

 

[Guest]

OPENXML is your best bet. Just as fast as BCP in all of my experiences. Better yet, you can conditionalize your inserts and updates.

 

[Danny Smith]

Thanks Clint.

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

OPENXML is your best bet. Just as fast as BCP in all of my experiences.

Better yet, you can conditionalize your inserts and updates.