2

I need to move the data from an existing SQL database to a new SQL database. I cant do a restore on the new SQL database because I need the tables to be generated through the migrations first. So I need a way to just move the data. I've tried using the SLQ Server Import Export Wizard but its a tedious process since I have to manually import a few tables at a time since the order matters. Does anyone know of a faster or more efficient way of importing data in this situation? Thanks in advance!

0

2 Answers 2

1

If right click on a database under "Tasks" you can "Generate Scripts...". When you get to the end of the wizard there is an Advanced option, under this an option "Types of data to script" from the drop down you can select "Schema and Data". This will create a script that will correctly generate tables and insert data.

0
1

BCP is a command line tool that is installed with SQL Server. It can dump all the data from a table to a disk file, or load a table from that disk file.

Write a script (PowerShell?) to iterate over the source's tables. Call bcp out *table*... to dump to file table.data. The mirror script can iterate over the disk directory and load the files to the target tables.

There are many options for controlling the file format (CSV, native etc.), using a query over a full dump, and so forth.

If the source & target tables have the same columns in the same order with the same type this is easy. If there are a few differences you can write manual queries for the exceptions. With many differences one can use table descriptor files though this becomes tedious to automate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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