tags:

views:

4009

answers:

6

I have 2 tables (srcTable1 & destTable) that have identical schemas. I am trying to copy all rows from srcTable to destTable and ignore the duplicates. I thought I could just add a WHERE clause with a subquery that would give me only the rows that aren't duplicates. However, it doesn't seem to work. I don't get any rows inserted or selected.

INSERT INTO destTable
SELECT * FROM srcTable
WHERE NOT EXISTS(SELECT * FROM destTable)

I realize I could do something like this:

INSERT INTO destTable
SELECT * FROM srcTable
WHERE MyKey IN (SELECT MyKey FROM destTable)

However, my tables have multiple keys and I can't think of how you could do this with multiple keys.

Any idea what I'm doing wrong or do you have any better ideas?

+7  A: 

Your problem is that you need another where clause in the subquery that identifies what makes a duplicate:

INSERT INTO destTable
SELECT Field1,Field2,Field3,... 
FROM srcTable
WHERE NOT EXISTS(SELECT * 
                 FROM destTable 
                 WHERE (srcTable.Field1=destTable.Field1 and
                       SrcTable.Field2=DestTable.Field2...etc.)
                 )

As noted by another answerer, an outer join is probably a more concise approach. My above example was just an attempt to explain using your current query to be more understandible. Either approach could technically work.

INSERT INTO destTable
SELECT s.field1,s.field2,s.field3,... 
FROM srcTable s 
       LEFT JOIN destTable d ON (d.Key1 = s.Key1 AND d.Key2 = s.Key2 AND...)
WHERE d.Key1 IS NULL

Both of the above approaches assume you are woried about inserting rows from source that might already be in destination. If you are instead concerned about the possibility that source has duplicate rows you should try something like.

INSERT INTO destTable
SELECT Distinct field1,field2,field3,... 
FROM srcTable

One more thing. I'd also suggest listing the specific fields on your insert statement instead of using SELECT *.

JohnFx
Your first solution did it.A JOIN doesn't do it becuase any JOIN only returns rows that the 2 tables have in common. What I need are the rows the 2 tables don't have in common.
Jeff Stock
Actually the Join will work, but only an OUTER join. Note the condition that d.key1 IS NULL meaning you want unmatched rows. Of course you'd have to tweak that condition for what exactly constitutes a duplicate in your scenario.
JohnFx
+3  A: 

Something like this?:

INSERT INTO destTable
SELECT s.* FROM srcTable s
LEFT JOIN destTable d ON d.Key1 = s.Key1 AND d.Key2 = s.Key2 AND...
WHERE d.Key1 IS NULL
Daniel Pratt
I tried this, but it doesn't work. A join, whether it is INNER or OUTER, will only give me rows the 2 tables have in common. I need the rows they don't have in common. I still have found no answer to this.
Jeff Stock
I'm glad you found something that works for you, but your statement is *not* correct. A LEFT (OUTER) JOIN will return all rows from the left-hand table, regardless of whether there is a matching row in the right-hand table. The WHERE clause eliminates all resulting rows where there is a match.
Daniel Pratt
+2  A: 

Have you tried SELECT DISTINCT ?

INSERT INTO destTable SELECT DISTINCT * FROM srcTable

Nick Josevski
I tried this and it doesn't work.DISTINCT will give me a list of distinct rows in the srcTable. What I need is a distinct list when the 2 tables are combined.
Jeff Stock
A: 

DISTINCT is the keyword you're looking for.

In MSSQL, copying unique rows from a table to another can be done like this:

SELECT DISTINCT column_name
INTO newTable
FROM srcTable

The column_name is the column you're searching the unique values from.

Tested and works.

Wadih M.
That might work if I were creating a new table, but the source table already exists, so it doesn't work.
Jeff Stock
A: 

Hey JohnFX maybe you can help me since what you described on this page is what I am trying to do. I tried the code below and I don't get any errors, but nothing is inserted into the destination table. **edit-sorry for some reason it wouldn't let me paste the code into the "insert code here" box shrug

CREATE PROCEDURE ConsultantAssociation
@ProjectNumber as VARCHAR(100)
AS
    INSERT INTO PRContactAssoc (ContactID)
    SELECT
        Projects_ConsultantAgreements.custConsultantContracted
    FROM
        Projects_ConsultantAgreements
    LEFT JOIN PRContactAssoc ON
        PRContactAssoc.ContactID = Projects_ConsultantAgreements.custConsultantContracted
    WHERE
        PRContactAssoc.ContactID IS NULL AND PRContactAssoc.WBS1=@ProjectNumber
GO
A: 

Can DesTable be more then one ?