1

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?

enter image description here

  • 1
    It does have a Save Script option. You can save the script to a file as the last step – Mark Sinkinson Aug 12 '15 at 14:29
  • As part of a data migration I would like to script a data transfer from the existing to a new database. - Why not use backup and restore (better to use tsql backup restore commands) ? – Kin Shah Aug 12 '15 at 14:30
  • @Kin The source and destination tables have some differences so the fields have to be mapped. – JohnnyBizzle Aug 12 '15 at 14:35
  • @MarkSinkinson looks like I am running Express version :( In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation. – JohnnyBizzle Aug 12 '15 at 14:40
  • @JohnnyBizzle Sorry, my bad. In my mind I thought you were using the 'Generate Scripts' wizard. – Mark Sinkinson Aug 12 '15 at 14:42
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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.