469,917 Members | 1,729 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Store multiple records at once in SQL database

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!

Mar 14 '07 #1
7 2216
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
"Pim75" <p.******@tiscali.nlwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
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!

Mar 14 '07 #2
On Mar 14, 10:20 am, "Pim75" <p.meg...@tiscali.nlwrote:
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()

Mar 14 '07 #3
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()

Mar 14 '07 #4
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" <p.******@tiscali.nlwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
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()

Mar 14 '07 #5
Thanks, works great!

Mar 14 '07 #6
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.

...


"Pim75" <p.******@tiscali.nlwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
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!

Mar 14 '07 #7
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 Kesting
Mar 14 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Christopher Brandsdal | last post: by
11 posts views Thread by dskillingstad | last post: by
4 posts views Thread by HLCruz via AccessMonster.com | last post: by
5 posts views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.