2

I have two SQL Server 2008 databases. One is for my old application (full of data) and the other is for a complete re-write of my application.

I want to migrate the data, but I have no idea how to tackle it. Some of the tables have exactly the same structure, but some tables in the new database are composed from columns of multiple tables in the old database.

Is there a way to create such a script/project/application in which I can define the data transformation while also maintain constrains?

closed as too broad by Jon Seigel, Mark Storey-Smith, Max Vernon, Mat, Taryn Sep 30 '13 at 13:57

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

You can use SSIS (from sql 2005 and up)to transfer data from one database.Table to anotherDatabase.Table or all tables as well.

Note: SQL Server Import and Export Wizard will give you option to save the package at the end so that you can reuse the package for subsequent data transfer.

You need to be careful with constraints on the destination database. You can temporarily disable them and then reenable then once your data load is done. Script by Aaron can be found at Temporarily disable all foreign key constraints

If the tables from source and destination have exactly same schema, then you can use bcp as well. for script refer to my answer here.

  • Should I still go for SSIS even if it's only a one time data transformation? Is it worth the learning time, or should I just stick to TSQL? – Tsury Sep 30 '13 at 0:25
  • @Tsury I would suggest to learn SSIS - very useful for automating SQL Server related tasks. – Kin Shah Oct 2 '13 at 20:23
1

First you need to define mapping between old and new schema

1- table mapping for better understanding of new and old structure

2- table+column mapping for data transformation

After having clear understanding of 2, then you can write ether SSIS possibly with some learning curve or even TSQL script if it is one off job and you just want to transfer data from old to new.

Good luck!

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