How to copy data of new tables, along with schema?

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

How to copy data of new tables, along with schema?

Postby v8maro » Thu May 29, 2014 3:27 pm

Using SC I'm trying to create a new table AND copy the data over, how do I do this? Example below:

DB1 (dev db)
T1
T2
T3

DB2 (prod db)
T1
T2

SC obviously gives me the differences between the two DB's, and as such I get a merge on T1 and T2, and a new create script on T3. However, T3 ha some static data in it that I need pushed to DB2. I tried to use SQL Data Compare and it states that there is nothing to compare it to so I cannot copy those rows.

How does one do this?

Also, to complicate it more, T3 now has a FK in T2. So a column is added to T2 with a default of ID 1(which ID 1 will exist in T3)...so when the merge script runs on T2, the T3 FK column should be populated with ID 1. How is this accomplished?

Thanks,

Steve
v8maro
 
Posts: 1
Joined: Thu May 29, 2014 3:04 pm

Postby Brian Donahue » Mon Jun 02, 2014 2:06 pm

Hi Steve,

First you have to push the change to the second database (CREATE TABLE T3). Once that is done, you should be able to use SQL Data Compare to compare and synchronize the tables.

If this does not work immediately, it's probably because T3 does not have a primary key or unique index that SQL Data Compare can use to identify unique rows of data (it shows up in "list of tables that could not be compared"). You can create a primary key on the table on both sides, or if schema modification is not allowed, you can go into SDC's Tables and Views and set a comparison key based on one or more columns there.

http://documentation.red-gate.com/displ ... +and+views

Hope this helps.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests