Is it possible to insert an entire VB.NET DataTable into a SQL Server at once
datatable, sql, sql-server, t-sql, vb.net
Solution
try with SqlBulkCopy
Problem
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?