CODEWITHSUNDEEP
sqlsql-serverprimary-keysql-insert
James
James

Reputation: 45

MS SQL - Insert Into Distinct Where NOT Exists issue

I have looked at other threads at Stack overflow in regards to copying data from one table to another (but don't copy over duplicate data). But I keep running into this issue.

I have table A and B.

Table A has 7 columns with the first column as it's key index. Table B has 7 columns with the first column as it's key index.

Here is the code that isn't working for me:

INSERT INTO dbo.A SELECT DISTINCT * from dbo.B
WHERE NOT EXISTS (SELECT * from dbo.A
WHERE 'dbo.B.c1' = 'dbo.A.c1')

Unfortunately, I get:

Violation of PRIMARY KEY constraint 'PK_A'. Cannot insert duplicate key in object 'dbo.A'. The duplicate key value is (VALUE).

This being due to it being a primary key.

My question is: how do I copy data from B to A without copying data that already exists in A.

Upvotes: 1

Views: 569

Answers (1)

Mureinik
Mureinik

Reputation: 311228

Single quotes denote string literals in SQL. So you are checking whether the string 'dbo.B.c1' is equal to the string 'dbo.A.c1', which, of course, is never true. Just remove them and you should be fine:

INSERT INTO dbo.A 
SELECT DISTINCT * 
FROM   dbo.B
WHERE  NOT EXISTS (SELECT * 
                   FROM   dbo.A
                   WHERE  dbo.B.c1 = dbo.A.c1 -- No quotes!
                  )

Upvotes: 2

Related Questions