sql conditional insert if row doesn't already

问题:

I'm creating a sproc that will insert rows into a 'staging' table with an insert into + subquery like so:

INSERT INTO myStagingTable
SELECT col1, col2, col3
FROM myRealTable

I need to put a conditional in there somehow to determine if the value from col1 for example already exists on myStagingTable, then don't insert it, just skip that row from myRealTable.

is this possible? If so, how would I structure that?

TIA

回答1:

INSERT
INTO    myStagingTable
SELECT  col1, col2, col3
FROM    myRealTable rt
WHERE   NOT EXISTS
        (
        SELECT  rt.col1, rt.col2, rt.col3
        INTERSECT
        SELECT  col1, col2, col3
        FROM    myStagingTable
        )

This will handle all duplicates (including NULL)

Note that is will insert the duplicates from the real table is any. Say if the real table contains

1 1 1
1 1 1

and the staging table contains

2 2 2

, both records with 1, 1, 1 will be inserted.

If you want to eliminate the duplicates on insert (so that only one instance of 1, 1, 1 is inserted), then just use this:

INSERT
INTO    myStagingTable
SELECT  col1, col2, col3
FROM    myRealTable
EXCEPT
SELECT  col1, col2, col3
FROM    myStagingTable