I have a database DBv1
on a SQL Server, and I want to migrate the data in it to DBv2
(on the same box, save server). DBv2
has very similar schema. The only difference in schema is that DBv2
has one less table, because it is de-normalized.
What would be the most practical way to migrate the data from DBv1
to DBv2
given that change in schema, and the need for some transformation?
Is there a tool that would do that for me? Or do I have to script that manually?
For clarification: this is a one-time load only since it is just a migration. By schema here I do not refer to the SQL object with the same name. Rather, I am referring to the structure of the tables. Due to de-normalization, one table is removed, and a new column is added to the parent table that would have the data in the deleted table.
Best Answer
You can use bcp out and bulk insert to achieve what you are trying to do.
You can use my script
Create a folder
D:\BCP_OUT\
locally on the server where the bcp out files will reside.