Import data from one Database to another script

Question

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?

Answer 1

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 BCP.EXE, 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: KeepIdentity, CheckConstraints, TableLock (allows the operation to happen in Bulk Logged mode), KeepNulls, FireTriggers, and UseInternalTransaction.
• optionally reporting the total number of rows migrated.
• executing a stored procedure as the source of the data instead of using a SELECT

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

Example:

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';