As part of a data migration I would like to script a data transfer from the existing to a new database. I used to do this with DTS on older versions of MS SQL. I am currently using SQL 2012. Note the tables have different some different fields. It would be nice if the import/export data tool had a save script option?
Another option that could handle this and possibly allow for a bit more flexibility is to use a SQLCLR function or stored procedure that makes use of the SqlBulkCopy class (which is essentially the same framework used by
BULK INSERT, etc). While writing such a function/procedure might not be worth the time/effort for a one-off project, there is fortunately such a stored procedure that already exists and is downloadable for free. It is called DB_BulkCopy and is part of the SQL# SQLCLR library (which I am the author of, but again, this stored procedure is in the Free version). It allows you to run any random query (that returns a result set, of course) and send the results to the local instance or a remote one (even to Oracle if need be). It also allows for:
- mapping columns if the destination has different names and/or ordering of the fields.
- setting a particular batch size.
- specifying the following options:
TableLock(allows the operation to happen in Bulk Logged mode),
- optionally reporting the total number of rows migrated.
- executing a stored procedure as the source of the data instead of using a
I am mentioning this because, as compared to running an external package, it is very easy to:
- update the parameters being sent to a stored procedure
- call a stored procedure in an ad-hoc fashion, or as part of a larger migration script, or scheduled from SQL Agent, etc.
- call a stored procedure in a loop to send multiple tables
EXEC SQL#.DB_BulkCopy @SourceQuery = N'SELECT t1.field1, t1.field5, t2.field12 FROM dbo.Table1 t1 INNER JOIN dbo.Table2 t2 ON t2.FKfield = t1.PKfield WHERe t2.field8 > something; ', @DestinationConnection = N'Server=ServerName\Instance; Trusted_Connection=true; Database=NewDB;', @DestinationTableName = N'MyNewTable', @BatchSize = 2000, @NotifyAfterRows = 100, -- print message per every 100 rows @TimeOut = 600, -- 10 minutes @ColumnMappings = N'field1,NewFieldA|field5,NewFieldB|field12,NewFieldC', @BulkCopyOptionsList = N'KeepIdentity|TableLock|KeepNulls';