如果行不存在，则 sql 条件插入 [英] sql conditional insert if row doesn't already exist

问题描述

我正在创建一个 sproc，它将行插入到暂存"表中，并像这样插入 + 子查询:

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

我需要以某种方式在其中放置一个条件来确定来自 col1 的值是否已经存在于 myStagingTable 中，然后不要插入它，只需从 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
        )

这将处理所有重复项(包括 NULL)

This will handle all duplicates (including NULL)

请注意，将插入来自真实表的重复项是 any.说如果真正的表包含

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

并且暂存表包含

2 2 2

, 1, 1, 1 的两条记录都会被插入.

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

如果您想在插入时消除重复项(以便仅插入 1, 1, 1 的一个实例)，则只需使用以下命令:

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

这篇关于如果行不存在，则 sql 条件插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！