Sql bulk insert and paramaters

Thursday, 18 September 2008 03:30 by administrator

Sometimes transferring data quickly and safely can be a major task. In the following example I’ll show you how you can do a parameterized insert and how to use the sql transaction. This will speed up your insert tremendously.

More detailed info on the sql transaction class:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

In the following example I loop through a dataset to insert the data back to my sql db. Its only three columns, but I’ve inserted 1.2 million records in less than 15 seconds

Dim cnn As New system.Data.SqlClient.SqlConnection("Connstr")
Dim da As New System.Data.Sqlclient.sqlDataAdapter

cnn.Open()
Using mytransaction As System.Data.Sqlclient.sqlTransaction = cnn.BeginTransaction
Using mycommand As System.Data.Sqlclient.sqlcommand = New System.Data.Sqlclient.sqlcommand(cnn)

                da.InsertCommand = mycommand
                Dim myparam As New System.Data.Sqlclient.sqlParameter
                Dim myparam1 As New System.Data.Sqlclient.sqlParameter
                Dim myparam2 As New System.Data.Sqlclient.sqlParameter

Dim i As Integer
mycommand.CommandText = "INSERT INTO [YourTable] (Col1,Col2,Col3) VALUES (?,?,?)"

                mycommand.Parameters.Add(myparam)
                mycommand.Parameters.Add(myparam1)
                mycommand.Parameters.Add(myparam2)

                For i = 0 To ds1.Tables(0).Rows.Count - 1
                                myparam.Value = Replace(ds1.Tables(0).Rows(i).Item(0).ToString, "'", "''")
                                myparam1.Value = Replace(ds1.Tables(0).Rows(i).Item(1).ToString, "'", "''")
                                myparam2.Value = Replace(ds1.Tables(0).Rows(i).Item(2).ToString, "'", "''")
                                mycommand.ExecuteNonQuery()
                Next