Store multiple records at once in SQL database

  • Thread starter Thread starter Pim75
  • Start date Start date
P

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!
 
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!
Click to expand...

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()
 
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()
 
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.

...
 
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!
Click to expand...

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
 
Back
Top