Insert using "not exists" in SQL server

Question

0.00/5 (No votes)

See more:

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')

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 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

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.

Posted 6-Nov-17 19:51pm

Member 13507201

Updated 6-Nov-17 21:03pm

Add a Solution

Comments

pt1401 7-Nov-17 2:50am

Why do you think you should only get 4 records?
SELECT * INTO #TEMP FROM TEST_DEL WHERE 1=0 inserts nothing, it just creates an empty temp table with the correct schema.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pt1401 · Answer 1 · 2017-11-06T21:03:00

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)

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.