SQL Update statement "apparently" duplicating rows
Posted on 2012-09-13
This has had me scratching my head for a while.
I have two tables ~50 columns x 350k+ rows in MSSQL Server 2000.
The two tables are almost identical in structure.
There are 3 important columns: The primary key column (“ABC_ID”), an “id” column (“id”) created by “others” and a datetime field with a getdate() default (“Created_On”). The remaining columns are a mix of char, decimal, text, datetime etc.
Table A contains “correct” data and Table B needs to be made a copy of Table A.
First I perform an Insert for all rows in Table A that are not in Table B – This works correctly.
Then I perform an Update for rows where the “id” columns are equal.
The behaviour that I am seeing is:
1. After the Insert there are no duplicate rows with the same values for “id”
2. After the Update there ARE duplicate rows with the same “id” but unique ABC_IDs
3. The duplicate rows have the SAME datetime value in “Created_On”
So, can anyone suggest what’s going on here? Naturally, I don’t want these duplications. The duplicate rows look identical with the exception of the PK.
For completeness I should mention that I also perform a Delete for rows in Table B but missing from Table A. Although I have excluded this operation from having any influence on the observed behaviour.
If I have missed some vital info I apologise.
Thanks in advance!