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?
Sql-server – Import data from one Database to another script
importscriptingsql-server-2012
Related Question
- Sql-server – SQL Server Import Job Removed Data After Job Failed
- Handling FK Constraints When Importing Data Using DTS Import/Export Wizard
- Import Oracle schema data without losing modifications of stored procedures
- Sql-server – SQL import data when both source and destination has data and identity column
- SQL Server – Error When Importing Data Using Import & Export Data Wizard
- Sql-server – Copying large amounts of data to another target tables
Best Answer
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:KeepIdentity
,CheckConstraints
,TableLock
(allows the operation to happen in Bulk Logged mode),KeepNulls
,FireTriggers
, andUseInternalTransaction
.SELECT
I am mentioning this because, as compared to running an external package, it is very easy to:
Example: