By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 16 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 16 '05 #2

P: n/a
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


Nov 16 '05 #3

P: n/a
OPENXML is your best bet. Just as fast as BCP in all of my experiences. Better yet, you can conditionalize your inserts and updates.
Nov 16 '05 #4

P: n/a
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.
Nov 16 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.