Sync only new rows in a table

Compares and synchronizes SQL database content.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Sync only new rows in a table

Postby sbacheld » Wed Apr 24, 2013 8:11 pm

Hi,

We are in the process of automating database updates from our dev -> staging -> production process. One of the tables that we want to use sql data compare for has different values for each of the different environments. So it would look something like this:

dev: name=Row1, timeout=1
staging: name=Row1, timeout=10
production: name=Row1, timeout=15

Now, if we add a new row, we want that new row to get synced to all the databases, but would like any existing rows to remain unaffected. Is this possible with sql data compare? We are running this from the command-line if that makes a difference

Thanks,
Sean
sbacheld
 
Posts: 4
Joined: Wed Apr 24, 2013 8:01 pm

Postby Brian Donahue » Thu Apr 25, 2013 9:59 am

Hi Sean,

Unfortunately I can't answer correctly without having the whole schema. SQL Data Compare matches rows based on the primary key or column(s) you choose to use as a row identifier. And it's not state-aware so it can't tell you if there are "new" rows. What you could possibly try is to use a WHERE clause on the tables and views tab and put a selection criteria there -- for instance if your table has a datetime column in it you could sync only records that are newer than 30 days by putting a WHERE clause in to compare only date > GETDATE()-30.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby sbacheld » Fri May 03, 2013 5:14 am

Thanks, Brian. We'll investigate a solution along those lines!
sbacheld
 
Posts: 4
Joined: Wed Apr 24, 2013 8:01 pm

Postby wdhenrik » Thu Jun 13, 2013 12:55 am

If "new" rows is defined as rows that don't exist in the other table, you can certainly do this with SQL Data Compare.

Your comparison results are broken down into rows that exist only in the left table (To Insert), rows in both (To update) and rows existing only in the right table (To Delete).

If your new rows are in the left table, only select the records that show up in that section and create your deployment script. You should end up with only insert statements in your deployment script.

I haven't used the command line for this, so I'll have to defer back to RedGate if the command line support the same options the GUI does.

Hope that helps.
wdhenrik
 
Posts: 12
Joined: Thu Jul 19, 2012 11:58 pm


Return to SQL Data Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests