Red Gate forums :: View topic - Insert and delete of same row
Return to www.red-gate.com RSS Feed Available

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

Insert and delete of same row

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
miljan



Joined: 12 Feb 2012
Posts: 1

PostPosted: Sun Feb 12, 2012 10:00 am    Post subject: Insert and delete of same row Reply with quote

Hello,

I'm facing one strange situation using SQL Data Compare 9. When I do the comparison of two tables with same structure, application shows the same row to be deleted and inserted (it can be found in both "Rows only in DB1" and "Rows only in DB2".
I've checked the tables, they are compared by primary key that is the same in both tables. Also, in DB2 the row that is marked for deletion does not exist, but it is anyway marked for deletion.
I assume that application does this in order to be sure that the row that is to be inserted isn't added to table in meantime so the script wouldn't report the error when it is executed, but this is not very suitable for my case when I get a couple of millions of rows with differences...
Is there some option or something that would tell the application not to add the rows that are to be inserted to the deletion list?

Also, I've noticed that some rows are added to both sides although they exist in both tables with identical data. Why is this happening?

Thanks!

Miljan
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Mon Feb 13, 2012 4:31 pm    Post subject: Reply with quote

Yes, that is very likely, since updates are batched into inserts and deletes. This is why Data Compare has the "disable primary keys" option to work around that.

Rows that can be matched by key ID should not be inserted or deleted. Typically this happens when you set a text field as the primary key and the collation differs, or on one side you have a variable-length column and a fixed-length column on the other. As a workaround, you can try the "Force binary collation" and "Trim trailing spaces" options, respectively.
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