Red Gate forums :: View topic - Inserting differences instead of updating them
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Inserting differences instead of updating them

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Authorized_



Joined: 02 Mar 2011
Posts: 5

PostPosted: Wed Mar 02, 2011 9:52 am    Post subject: Inserting differences instead of updating them Reply with quote

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_
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Thu Mar 03, 2011 7:19 pm    Post subject: Reply with quote

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

Code:
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
Back to top
View user's profile Send private message Send e-mail
Authorized_



Joined: 02 Mar 2011
Posts: 5

PostPosted: Mon Mar 07, 2011 8:37 am    Post subject: Reply with quote

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
}
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Mon Mar 07, 2011 4:01 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Authorized_



Joined: 02 Mar 2011
Posts: 5

PostPosted: Tue Mar 08, 2011 8:44 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group