Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

up vote 1 down vote accepted

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.

share|improve this answer
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 Oct 2 '13 at 20:23

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!

share|improve this answer

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