使用“不存在”插入在SQL服务器中 [英] Insert using "not exists" in SQL server

问题描述

大家好，



我正面临一个问题，即尝试使用不存在的值将值插入临时表。



下面是我创建的虚拟表：

Hi All,

I am facing an issue whil trying to insert values into a temp table using not exists.

Below is the dummy table that i created:

CREATE TABLE TEST_DEL(ID INT, NAME VARCHAR(10))

虚拟插页：

dummy inserts:

INSERT INTO TEST_DEL VALUES(1, 'A')
INSERT INTO TEST_DEL VALUES(2, 'B')
INSERT INTO TEST_DEL VALUES(2, 'C')
INSERT INTO TEST_DEL VALUES(3, 'D')
INSERT INTO TEST_DEL VALUES(4, 'E')

创建一个临时表结构来获取一个记录Test_DEL表中每个'ID'值：

creating a temp table structure to get one record for each 'ID' value from Test_DEL table:

SELECT * INTO #TEMP FROM TEST_DEL WHERE 1=0

i准备了以下查询，将一条记录插入临时表中，每个来自Test_DEL的id：

i prepared the following query to insert one record into temp table for each id from Test_DEL:

INSERT INTO #TEMP (ID, NAME)
SELECT  a.ID, a.NAME FROM TEST_DEL A 
WHERE NOT EXISTS(SELECT 1 FROM #TEMP B WHERE B.ID = A.ID

这里我对此查询的期望是检查每条记录在插入临时表之前使用id值，如果它不存在则插入，如果临时表中存在ID值的任何记录，则从插入中跳过该记录。



我应该得到4条记录到#temp我正在获得所有5条记录。



任何人都可以建议在我的查询中有什么错写道？



提前致谢。



我尝试过的事情：



尝试使用不存在的上述插入语句，但它不起作用。

here my expectation with this query is to check for each record with id value before inserting into temp table, if it doesn't exist already then insert, and if any record exists with ID value in temp table, skip that record from inserting.

I am supposed to get 4 records in to #temp bu i am getting all the 5 records.

can anyone please suggest whats wrong in the query that i wrote?

Thanks in advance.

What I have tried:

Tried the above insert statement with "not exists" but it isn't working.

推荐答案

好的，ID = 2有两个实例。



你得到全部5的原因是在插入语句之前临时表中不存在ID = 2 （并记住你只有一个插入声明，而不是4或5个单独的声明。）



尝试自己运行SELECT： -

Ah ok, there are two instances to ID=2.

The reason you get all 5 is that ID=2 does not exist in the temp table before the insert statement (and remember you only have a single insert statement, not 4 or 5 individual ones).

Try running the SELECT on it's own:-

SELECT  a.Id, a.NAME FROM TEST_DEL A 
WHERE NOT EXISTS(SELECT 1 FROM #TEMP B WHERE B.Id = A.Id)

Id          NAME
----------- ----------
1           A
2           B
2           C
3           D
4           E

(5 row(s) affected)

您可以将插入视为两阶段操作： -

1.选择TEST_DEL中临时表中不存在的所有行。

2.将这些行插入临时表。

You can think of the insert as a two-stage operation:-
1. SELECT all the rows in TEST_DEL that don't exist in the temp table.
2. INSERT those rows into the temp table.

这篇关于使用“不存在”插入在SQL服务器中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！