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?

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
@JohnnyBizzle since saving is not an option due to Express version, you can use bcp out and bulk insert into destination. — Kin Shah, Aug 12 '15 at 14:49
Looking into the problem further, I am using Standard Edition (64-bit) so I probably need to re-install SSMS as suggested here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175688 — JohnnyBizzle, Aug 12 '15 at 15:47
I also discovered that the version installed on the database server has Save script option so I might just work there! Another note: the import export data app lives in "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012". Maybe there are a mixture of versions living there! — JohnnyBizzle, Aug 12 '15 at 16:05

Solomon Rutzky · Answer 1 · 2020-02-20T23:56:48.637

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

Import data from one Database to another script

1 Answers1

Linked