Inserting differences instead of updating them

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Inserting differences instead of updating them

Postby Authorized_ » Wed Mar 02, 2011 9:52 am

Hello,

I'm writing a small tool that synchronizes the databases automatic between the empty master and the target, which is running at our clients, and contains important data.

The master database contains only some basic values.
Our clients can add and as many rows they want.

So, in some cases, data on the target may not be deleted or updated. Instead of the delete or update, the program needs to insert a new row.

But how do you do that in the sdk?

Thanks in advance,
Authorized_
Authorized_
 
Posts: 5
Joined: Wed Mar 02, 2011 9:38 am

Postby Chris Auckland » Thu Mar 03, 2011 7:19 pm

You should be able to stop any updates or deletes using something like:

Code: Select all
foreach (Difference difference in stagingVsProduction)
                {
                    if (difference.Type == DifferenceType.OnlyIn1)
                    {
                    difference.Selected = true;
                    } else
                    difference.Selected = false;                   
                }


This will mean that only new rows will be inserted on the target from the source.

I hope this helps.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby Authorized_ » Mon Mar 07, 2011 8:37 am

Hi,

Thank you for your reply Chris.

I understand what you're trying to do. but the difference will not be executed by using this code. But I want the difference to be executed in an insert-statement instead of doing an update.

something like
if (difference.querytype == querytype.update)
{
difference.querytype = querytype.insert
}
Authorized_
 
Posts: 5
Joined: Wed Mar 02, 2011 9:38 am

Postby Chris Auckland » Mon Mar 07, 2011 4:01 pm

Thanks for your reply.

If you're hoping to insert the rows as new rows rather than updating the existing rows, then unless you reassigned the PK for the new rows, you would probably encounter duplicate key violations when you ran the script. In other words, if the default is to update the row with ID 15, if you instead tried to insert a row with ID 15 then the script would fail as ID 15 already exists.

There isn't a way in SQL Data compare to reassign a primary key value, but you could try using a different comparison key in the project and also exclude the column you were using as a key before. If the PK column is excluded from the project, then SQL Server will assign a value when the row is inserted, but how this would match up with any other related rows is difficult to say.

It sounds like you're almost wanting to merge data, which is pretty difficult to do with SQL Data Compare, as it's primarily a synchronization tool.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby Authorized_ » Tue Mar 08, 2011 8:44 am

Hi,

I am aware of the problems with the primary keys, references and stuff.
But that isn't a problem in our database.

Merging is in fact the correct word.
But I think I'm going to create a "SQLInsertStatementBuilder" and execute the statements through ADO.net.

It's maybe an idea for an next version of SQL Data Compare.
Authorized_
 
Posts: 5
Joined: Wed Mar 02, 2011 9:38 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests