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

问题描述

我正在创建一个存储程序，该存储程序将在带有插入+子查询的'staging'表中插入行，如下所示：

 插入myStagingTable 
从myRealTable 
  

中选择col1，col2，col3
我需要以某种方式在其中放置条件，以确定例如col1的值是否已存在于myStagingTable上，然后不插入它，只需从myRealTable中跳过该行即可。

可能吗？如果是这样，我将如何构建它？

TIA

解决方案

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


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