更新时间:2023-10-10 22:49:34
将
中选择col1,col2,col3
插入myStagingTable
SELECT col1,col2,col3
从myRealTable rt
不存在
(
SELECT rt.col1,rt.col2,rt.col3
INTERSECT
从myStagingTable
)
pre>这将处理所有重复项(包括
NULL
)请注意,这将从真实表中插入重复项。说实表是否包含
1 1 1
1 1 1,并且登台表包含
2 2 2
,两个记录的
1、1、1
将被插入。如果要消除插入时的重复项(因此,只有一个
1、1、1 插入code>),然后只需使用以下命令即可:
INSERT
INTO myStagingTable
SELECT col1,col2,col3
从myRealTable
除
SELECT col1,col2,col3
从myStagingTable
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
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