Menu
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?
942
The T-SQL function OUTPUT, which was introduced in 2005, can be used to insert multiple values into multiple tables in a single statement. The output values of each row that was part of an INSERT, UPDATE or DELETE operation are returned by the OUTPUT clause.
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
Simple INSERT statement to add data to the table. Use INSERT Statement to add multiple rows in the table. INSERT INTO SELECT clause to insert the output generated by the SELECT query. INSERT IGNORE clause to ignore the error generated during the execution of the query.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
