Store multiple records at once in SQL database

[Pim75]

Hello,

I want to store multiple records at once in a SQL database with a
For..Next instruction like the sample code below:

For counter = 0 To 100
Dim dbInsert As New SqlCommand( _
"INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
Field2 & "')", _
myConnection)

dbInsert.Connection.Open()
dbInsert.ExecuteNonQuery()
dbInsert.Connection.Close()
Next

In the code above the query to add the record to the database is
executed 100 times. I would like to know if there's a more efficient
way to add 100 records at once to the table?

Any help is appreciated!

 

[Eliyahu Goldin]

This is fine except you don' need to reopen connection on every insert. Just
open it once before the loop and close after.

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net

 

[Guest]

Hello,

I want to store multiple records at once in a SQL database with a
For..Next instruction like the sample code below:

For counter = 0 To 100
Dim dbInsert As New SqlCommand( _
"INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
Field2 & "')", _
myConnection)

dbInsert.Connection.Open()
dbInsert.ExecuteNonQuery()
dbInsert.Connection.Close()
Next

In the code above the query to add the record to the database is
executed 100 times. I would like to know if there's a more efficient
way to add 100 records at once to the table?

Any help is appreciated!

If you are using .NET 2/ADO.NET 2, there is a new feature named
SqlBulkCopy that lets you to perform copy operation for a large amount
of data between a source data store and a destination.

For example, when your destination table is

CREATE TABLE [dbo].[test] (
[col1] [nvarchar] NULL ,
[col2] [nvarchar] NULL ,
) ON [PRIMARY]
GO

a code for SqlBulkCopy could be following

Dim dt As DataTable = New DataTable()

dt.Columns.Add(New DataColumn())
dt.Columns.Add(New DataColumn())

Dim row As DataRow = dt.NewRow()

For counter = 0 To 100
row(0) = ....
row(1) = ....
dt.Rows.Add(row)
Next

Dim cn As SqlConnection = New SqlConnection(".......")
Dim bc As System.Data.SqlClient.SqlBulkCopy = New
System.Data.SqlClient.SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock,
Nothing)

bc.BatchSize = dt.Rows.Count
cn.Open()
bc.DestinationTableName = "test"
bc.WriteToServer(dt)
cn.Close()
bc.Close()

 

[Pim75]

Hello Eliyahu,

Thanks for your help.
As I'm new to asp.net it's not clear to me how I can put the Open()
and Close() instruction outside the For.. Next commands.

I can make something like the code below but that won't work because
the dbInsert is declared after the dbInsert.Open in this case. Can you
help me to get this work correct?

dbInsert.Connection.Open()
For counter = 0 To 100
Dim dbInsert As New SqlCommand( _
"INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '"
&
Field2 & "')", _
myConnection)
dbInsert.ExecuteNonQuery()
Next
dbInsert.Connection.Close()

 

[Eliyahu Goldin]

You have already a separate connection object myConnection. Just do
myConnection.Open() and myConnection.Close() instead of
dbInsert.Connection.Open()/Close().


--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net

 

[Pim75]

Thanks, works great!

 

[sloan]

If you're using Sql Server 2000 or beyond, there is a better way.

See:
http://support.microsoft.com/kb/315968


but basically you can:

Create a strong typed dataset.
Add rows to a table in the dataset.
Send the DataSet.GetXml() into the stored procedure.

The extra bonus. If you have any indices (indexes), they get rebuild AFTER
all N number of rows are inserted.

...

 

[Hans Kesting]

Hello,

I want to store multiple records at once in a SQL database with a
For..Next instruction like the sample code below:

For counter = 0 To 100
Dim dbInsert As New SqlCommand( _
"INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
Field2 & "')", _
myConnection)
dbInsert.Connection.Open()
dbInsert.ExecuteNonQuery()
dbInsert.Connection.Close()
Next
In the code above the query to add the record to the database is
executed 100 times. I would like to know if there's a more efficient
way to add 100 records at once to the table?

Any help is appreciated!

If you change the command to use parameters (safer!), you only have to change
the
values of those parameters. Then you can execute the command again.

Hans Kestin