CODEWITHSUNDEEP
sqlsql-servervb.nett-sqldatatable
SqlRyan
SqlRyan

Reputation: 33914

Is it possible to insert an entire VB.NET DataTable into a SQL Server at once

I have a SQLClient.DataSet in VB.NET, and I want to insert the entire thing into a SQL Server table without having to do the following:

For Each dr as Datarow in MyDataset
  Dim sc As New SqlCommand("INSERT INTO MyNewTable " & _
                            "VALUES (@column1, @column2)", MyDBConnection)
  sc.Parameters.AddWithValue("@column1", dr.Item(0))
  sc.Parameters.AddWithValue("@column2", dr.Item(1))
  sc.ExecuteNonQuery()
Next

Since I've got close to a million rows (all pretty skinny, so it's not much space), I obviously don't want to run this loop and generate a million INSERT statements.

I know that one option is to use a linked server when I initially fetch the data, since it's coming from another SQL Server, and just have it to the INSERT from there. However, if I already have the data in my application, is there a more efficient way to bulk insert it? Can I somehow pass the DataTable as a parameter to SQL Server and have it sort it out and insert the rows?

Upvotes: 11

Views: 59021

Answers (5)

Yannick
Yannick

Reputation: 41

A way simplier way is to use a table adapter. Then you can use the Fill method to give a datatable as argument :

    Dim oStronglyTypedTable As StronglyTypedDataTable = GetTable() 'A custom function that creates your table from wherever you want)
    If Not oStronglyTypedTable Is Nothing Then
        Using oAdapter As New StronglyTypedTableAdapter
            Dim res As Integer = oAdapter.Update(oStronglyTypedTable)
            MsgBox(res & " rows have been updated.")
        End Using
    End If

Do not forget to change your Database "Copy to Output Directory" property to "Do net copy" and set your connection string properly...

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294287

With SQL Server 2008 you can use Table-Valued Parameters:

Dim sc As New SqlCommand(
  "INSERT INTO MyNewTable (field1, field2,...)"&
    "SELECT field1, field2,... FROM @MyTable;", MyDBConnection) 
sc.Parameters.AddWithValue("@MyTable", MyDataset)  
sc.ExecuteNonQuery()

Upvotes: 8

C-Pound Guru
C-Pound Guru

Reputation: 16368

Use the SqlDataAdapter's InsertCommand to define your Insert query. Then call the DataAdapter's Update Method with your dataset as a parameter to have it push the data.

Something like:

Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter

DA.InsertCommand = New SqlCommand("Insert Into tbl1(fld0, fld1, fld2) Values(@fld0, @fld1, @fld2)", conn)
Parm = DA.InsertCommand.Parameters.Add(New SqlParameter ("@fld0", NVarChar, 50, "fld0"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld1", SqlDbType.NVarChar, 50, "fld1"))
Parm = sqlDA.InsertCommand.Parameters.Add(New SqlParameter ("@fld2", SqlDbType.NVarChar, 50, "fld2"))
DA.Update(dataset1, "tbl1")

Upvotes: 3

CSharpAtl
CSharpAtl

Reputation: 7512

You could call .WriteXML() on the DataSet and dump that into the database in one insert.

Upvotes: 2

anishMarokey
anishMarokey

Reputation: 11397

try with SqlBulkCopy

Upvotes: 19

Related Questions