2

I came to know how to copy data from one database to another database within one server, from this Transfer data from one database to another database and between different servers from this Copy table to a different database on a different SQL Server, but I want to copy data of table from one database to another database which has different table structures.

I will explain my requirements with an example.

Consider a table contains basic information of patients such as FirstName, LastName, Gender, Contact, Address, OpShortNotice, HasDiabetes, HasBloodPressure and so on.

In source database it has table named Patient and contain all the above fields, where as in my destination database has FirstName, LastName, Gender, Contact, Address fields in the table named Person and OpShortNotice, HasDiabetes, HasBloodPressure fields in the table named Patient. Then how to copy data from source to destination database?

Like above simple example scenario, I have many tables and massive data to be migrated. As of now, my source and destination are in same server to make a trail of Data migration but definitely my future requirement will be with different servers.

Please, Anyone help me to do this with scripts or any particular tool meeting my requirements.

Thank you, in advance.

Community
  • 1
  • 1
prakash2089
  • 498
  • 4
  • 16
  • 1
    Have you looked at the [SQL Server Integration Services](http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx)? – Yao Bo Lu Jan 16 '14 at 08:19
  • @YaoBoLu: Sorry for the late reply. Now working that in SSIS and have doubts which is posted in the question http://stackoverflow.com/questions/21276004/how-to-tackle-not-null-constraints-in-ole-db-destination-in-ssis-package Will u please look at that. – prakash2089 Jan 22 '14 at 06:49

2 Answers2

1

SQL Server integration services is designed specifically to do this.

it is primarily used for data warehousing where data is taken from one (or mulitple ) database and stored in another, differently designed database.

in the case of data warehousing the second database is normally designed for better data retrieval and reporting access and less concerned with normalisation.

often this process will use an intermediary to allow for multi stage transformation

this process is often called ETL (Extract transform and Load)

RoughPlace
  • 1,111
  • 1
  • 13
  • 23
  • Now I'm working that in SSIS and have doubts which I have posted that in http://stackoverflow.com/questions/21276004/how-to-tackle-not-null-constraints-in-ole-db-destination-in-ssis-package will u please help me with that? – prakash2089 Jan 22 '14 at 06:51
0

In this case, you probably need to set up an intermediary ("translation") database which contains data structures from both sides plus a software which loads the data from the source, transforms it and then saves it in a staging area where you can check the results of the transformation process.

If the output looks good, you can load it in the target system.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • Now I'm working that in SSIS and have doubts which I have posted that in http://stackoverflow.com/questions/21276004/how-to-tackle-not-null-constraints-in-ole-db-destination-in-ssis-package will u please help me with that? – prakash2089 Jan 22 '14 at 06:53