Thursday, August 13, 2009

SQL Bulk copy, is much better then execute insert scripts



Copy whole table:

1. Generate insert script by loop the table rows, execute it



Simple, anyone can do it



2. SqlDataAdapter.Update(table)



Dim builder As New SqlCommandBuilder(adapter)
adapter.InsertCommand = builder.GetInsertCommand(False)



For Each tmpRow As DataRow In dtResult.Rows
tmpRow.SetAdded()
Next
adapter.Update(dtResult) 'Insert data here



3. SqlBulkCopy



Dim bCopy As New SqlBulkCopy(ConnExpress)
bCopy.DestinationTableName = tbName
bCopy.WriteToServer(dtResult)



4. SMO (Microsoft.SqlServer.Management.Smo) (Microsoft.SqlServer.Management.Common)



No comments: