Prevent Primary Key Violations!

[Savil]

Hi All
I have two tables that both contain the same multiple field primary key which are related with a one to one relationship. Table1 receives a monthly update which may or may not have additional rows but needs to be synchronised with table2. In access if duplicate value/primary key violations occured during an append query the guilty rows were ignored so by turning off warnings the synchronisation of the two tables happened without any fuss. If I read into this correctly, if SQL server encounters any violations it suspends the query and does not append any rows. My question is what is the best way to synchronise two tables with table1 appending any rows that are not in table2 and ignoring one that are already there.

Thanks

 

[zhavic]

Let's say, the table_X is table, from which you want to append rows and ID is primary key column

simple example

INSERT INTO table1
SELECT * FROM table_X
WHERE table_X.ID NOT IN ( SELECT ID FROM table2 )


--------------------------------
faster example ( i thing, because NOT IN clause is slower in this context )

ISERT INTO table1
SELECT * FROM table_X
LEFT JOIN table2 ON table2.ID = table_X.ID
WHERE table2.ID IS NULL

to be fast, needs INDEX on ID columns in tables

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[tlbroadbent]

Zhavic is correct that the left join query will perform much faster than the NOT IN query. You could also use WHERE NOT EXISTS but it is usually slower than the LEFT JOIN query. I include it here only to show another available method.

INSERT INTO table1
SELECT * FROM table2
WHERE Not Exists (Select * From table1 Where ID = table2.ID)

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[r937]

zhavic shows three tables, but terry's correctly only two

the original problem is an append with a check against the second table

and there's a difference, or at least i always thought so, between a NOT IN and a NOT EXISTS

plus, i think in savil's question it is table2 that is getting rows appended, so it would be INSERT INTO TABLE2

but i'm easily confused, so it might just be me...


rudy

 

[tlbroadbent]

Rudy,

Thanks for pointing out the errors in our posts. Indeed, the original question asks about inserting into table2. I switched the tables. Should be more careful.

--Corrected NOT EXISTS query
INSERT INTO table2
SELECT * FROM table1
WHERE Not Exists
(Select * From table2 Where ID = table1.ID)

In addition, you are correct regarding zhavic's post which I hadn't read carefully.

--Corrected LEFT JOIN query
INSERT INTO table2
SELECT * FROM table1
LEFT JOIN table2
ON table1.ID = table2.ID
WHERE table2.ID IS NULL

Finally, NOT IN doesn't work properly if the column selected in the sub-query contains NULLs. No rows will be returned if there is one or more occurences of NULL. This is explained in an Ron Talmage article you can read at the following location.

http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/CEEA6CCC09585570852568E20077EC44

If the column in question is the Primary Key then NOT IN should give the same result as the other two queries.

INSERT INTO table2
SELECT * FROM table1
WHERE table1.ID NOT IN (SELECT ID FROM table2)

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Savil]

Thanks to all for your replies but the plot thickens. I feel I didn't explain the problem sufficiently to start with so I will try again with the data changed to protect the innocent, here goes

tblSource columns
RegNo,Year,Mark,LocalID, - Several other columns with one column called Class

tblDestination columns
RegNo,Year,Mark,LocalID - Several other columns

RegNo,Year,Mark,LocalID are a multi field primary key in both tables with a one to one relationship.

tblSource is replaced monthly with fresh data
tblSource will likely contain records that are already in tblDestination but maybe new ones aswell.

I wish to append only RegNo,Year,Mark,LocalID into tblDestination where they do not exist and where the column class = 'something'

Thanks for your patience but once I find out how to tackle this problem efficiently I will never need to ask again.

Savil

 

[r937]

thickens, indeed

since tblDestination has other columns, but you want only to append a row with the pk columns, then all those other columns had better have defaults defined

using the NOT EXISTs syntax,

[tt]INSERT
INTO tblDestination
( RegNo,Year,Mark,LocalID )
SELECT RegNo,Year,Mark,LocalID
FROM tblSource
WHERE Not Exists
( Select 1
From tblDestination
Where RegNo = tblSource.RegNo
and Year = tblSource.Year
and Mark = tblSource.Mark
and LocalID = tblSource.LocalID
)
and Class = 'something'[/tt]

rudy

 

[Savil]

Rudy
Thanks for the post it works perfectly, however I am little curious as to why if I add a second where class = 'something' ie

WHERE Class = 'A' OR Class = 'B'
I once again recieve primary key violations

Forever in your debt but slightly baffled

Savil

 

[r937]

you can add additional class conditions, but you have to make sure you parenthesize your ANDs and ORs properly!

this is wrong --

[tt]WHERE Not Exists ( matching pk )
and Class = 'A' OR Class = 'B'[/tt]

and the reason is because it is evaluated as --

[tt]WHERE (
Not Exists ( matching pk )
and Class = 'A'
)
OR Class = 'B'[/tt]

whereas what you want is this (correct) --

[tt]WHERE Not Exists ( matching pk )
and (
Class = 'A'
OR Class = 'B'
) [/tt]

rudy

 

[Savil]

Thanks once again Rudy, you've come up trumps again. I am currently going through Access to SQL puberty so I keep tripping up over these little gotchas

I think we've done this this thread to death.

Thanks again