Key constraint issue

[DomTrix]

Hi, I have two tables both containing data under the same column headings and types, code and name. The name column is a unique index with an insert and update constraint.

I am trying to insert data from table2 to table1 when the data does not already exist in table1 but am getting a unique key constraint error (cannot insert duplicate key). My code is as follows:

INSERT INTO table 1
SELECT  table2.code,
        table2.description
FROM    table2
WHERE   NOT EXISTS (
           SELECT   table1.code 
           FROM     table1
           WHERE    table1.name = table2.name

Curiously, I can use the following, practically identical, code to copy the data one row at a time with no error:

INSERT INTO table 1
SELECT  TOP 1
        table2.code,
        table2.description
FROM	table2
WHERE 	NOT EXISTS (
           SELECT   table1.code 
           FROM     table1
           WHERE    table1.name = table2.name

Any ideas?

DT

 

[SQLDenis]

You said name is an unique index but you are inserting description
Should this
INSERT INTO table 1
SELECT table2.code,
table2.description

be this
INSERT INTO table 1
SELECT table2.code,
table2.name



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DomTrix]

ah yes, typo, should be:

INSERT INTO table 1
SELECT  table2.code,
        table2.name
FROM    table2
WHERE     NOT EXISTS (
           SELECT   table1.code 
           FROM     table1
           WHERE    table1.name = table2.name )

 

[SQLDenis]

Do you still get the error?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DomTrix]

I should elaborate, sorry for double post, cant seem to be able to edit posts.

It was a typo in the post, that is not what is producing the error.

Thanks,

DT

 

[SQLDenis]

what about this?
INSERT INTO table1
SELECT t2.code,
t2.name
FROM table2 t2 left outer join table1 t1 on t1.name = t2.name
and t1.code =t2.code
where t1.name is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DomTrix]

No, it produces the same error. I tried it with some test data that I set up and it works fine. It is failing on a particular set of data (a clients monthly db update).

Oh, duh, just found out the reason while explaining the problem. Table2 does not use an index and contained duplicates! Table2 is created by a third party whos databases arent too clean!

Thnx for you help tho

DT