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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Danny Smith" <da**************@hotmail.com> wrote in message
news:uP**************@tk2msftngp13.phx.gbl...

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

[shrini]

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

shrini

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:e6**************@TK2MSFTNGP12.phx.gbl...

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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Danny Smith" <da**************@hotmail.com> wrote in message
news:uP**************@tk2msftngp13.phx.gbl...

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

[Clint Hill]

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

"Clint Hill" <cl********@msn.com> wrote in message
news:B3**********************************@microsof t.com...

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

Better yet, you can conditionalize your inserts and updates.