Looking for a tool to append table rows in one database to the same table in another database
BernieHillier
Posts: 2 New member
I have 2 databases that have identical schema objects including tables that contain data from a number of years of live use, I am looking to combine the two databases. My approach is to manually make as much static data, like types for example, the identical in both database and then append eg. db1.table1 data to the bottom db2.table1 while maintaining relationships to other tables.
Eg. db1.Item has 100 rows (Id's 1-100), db2.Item has 200 rows (Id's 1-200). I need db3.Item with 300 rows (Id's 1-300).
In addition I have child tables of Item that will also need to have their rows combined and relationships maintained.
Does SQL Data Compare provide this functionality or are there any tools out there that do?
Eg. db1.Item has 100 rows (Id's 1-100), db2.Item has 200 rows (Id's 1-200). I need db3.Item with 300 rows (Id's 1-300).
In addition I have child tables of Item that will also need to have their rows combined and relationships maintained.
Does SQL Data Compare provide this functionality or are there any tools out there that do?
Tagged:
Answers
From your description, SQL Data Compare may be of help you.
SQL Data Compare is a comparison tool that matches rows of data using a comparison. It will automatically set a comparison if the pair of tables to be compared have a primary key or unique index or unique constraint.
SQL Data Compare will display a set of results that confirm the following:
- Rows of data that exist only in the source database.
- Rows of data that exist only in the target database.
- Rows of data that exist in both but different.
- Rows of data that are identical - requires the Comparison behavior option 'Show identical values in results' to be enabled.
So using your example:SQL Data Compare can only compare one pair of databases at a time, so you need the workout the best combination of comparisons to obtain the static data you require.
One golden rule to follow:
ALWAYS PERFORM A BACKUP OF THE TARGET DATABASE BEFORE DEPLOYING ANY CHANGES TO THE TARGET.
This will allow you to reset back if you encounter any problems.
You will find the help documentation available for SQL Data Compare available in this link.
Many thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
The following example uses the "Item" table which has a primary key on the ID column
We have:
db1.Item.ID = 1 to 10
db2.Item.ID = 1 to 10
db1 and db2 records are in no way related, they just happen to have the same PK value in two separate systems and respective databases.
In the end, I need to append the rows together like the following:
db1.Item.ID
1 <-- from db1 (and all of the other rows currently in the db1.Item table)
...
10 <-- last row in db1.Item
11 <-- this would be row 1 from db2.Item table
...
20 <-- last row in db2.Item
Even though the two Item tables have the same structure, the rows in each do not relate to each other and in the end I need to have all of the rows in the same Item table. Of course this a simplified example, there are many tables involved.
sorry for the delay in replying.
SQL Data Compare will not help you append the data in the manner that you are seeking. You will need to modify the data value ID column in your example in db2 and db3 outside of SQL Data Compare. Then you will be able compare db 2 with db1 and db3 with db1 to then deploy the data that exists only in db2 and db3 to db1.
Sorry.
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com