CODEWITHSUNDEEP
sqlsql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Insert using parameters where not exists

The following query does not fail but does not insert the unique values either.

I only want to insert unique values 

DECLARE @user_id AS varchar(80) 
DECLARE @real_name as varchar(250)

--New Unique values are "aaa" and they do not exist in the target table

SET @user_id = 'aaa'
SET @real_name = 'aaa'

INSERT INTO TargetTable
  ([user_id],real_name)
SELECT @user_id, @real_name
WHERE NOT EXISTS
  (SELECT [user_id],real_name FROM TargetTable)

Upvotes: 0

Views: 1093

Answers (4)

Brian White
Brian White

Reputation: 1314

This is to address user92546's concern. The insert happens inside the same query that queries TargetTable. The only subselect is against variables, and they are obviously not going to change in the course of this insert, while the table could change at any micro-second. 

DECLARE @user_id AS varchar(80)  
DECLARE @real_name as varchar(250)  

 --New Unique values are "aaa" and they do not exist in the target table
SET @user_id = 'aaa' 
SET @real_name = 'aaa' 

INSERT INTO TargetTable ([user_id],real_name) 
SELECT T1.user_id, T1.real_name 
FROM (select @user_id [user_id], @real_name [real_name] ) T1
LEFT JOIN TargetTable T2
   on T1.user_id = T2.user_id
     and t1.real_name = T2.real_name
     and T2.user_id = @user_id
     and T2.real_name = @real_name
WHERE T2.user_id is null

Upvotes: 0

HABO
HABO

Reputation: 15816

In case anyone still cares, or looks at this question later ... .

The original problem was that the OP used a WHERE NOT EXISTS clause that checked for any row existing in the target table, not a specific row. All it takes is checking for the data that you want to insert:

DECLARE @user_id AS varchar(80)  
DECLARE @real_name as varchar(250)  

 --New Unique values are "aaa" and they do not exist in the target table
SET @user_id = 'aaa' 
SET @real_name = 'aaa' 

INSERT INTO TargetTable 
  ([user_id],real_name) 
SELECT @user_id, @real_name 
WHERE NOT EXISTS 
  ( SELECT 42 FROM TargetTable WHERE [user_id] = @user_id AND real_name = @real_name )

The (currently) accepted answer encourages an unsafe design where two separate queries are executed without benefit of a TRANSACTION. Changes occuring between the queries may result in unpleasant results.

Upvotes: 0

MilkyWayJoe
MilkyWayJoe

Reputation: 9092

If you're having problem with the insert the way you doing, try a different approach like this:

-- if the count of records with that @user_id and @real_name is less or equal to 0, 
-- that means it's not in the table
IF (0 >= (SELECT COUNT(*) FROM TargetTable WHERE @user_id = [user_id] AND @real_name = [real_name]))
BEGIN
   -- then call a normal insert
   INSERT
     INTO TargetTable ([user_id], real_name )
   VALUES             (@user_id , @real_name)

END

Upvotes: 1

Diego
Diego

Reputation: 36136

the only way the query will insert a row is if there isn't a row with 'aaa', 'aaa' on the database already.

see, you are doing

SELECT 'aaa','aaa'
WHERE NOT EXISTS
(SELECT [user_id],real_name FROM TargetTable)

so, its the 'aaa','aaa' result set "minus" or "except" the result set formed by columns user_id and real_name of every row that's on target table.

So if 'aaa','aaa' already exist on the second result set, it will be excluded from the final result set, thus wont be inserted

Upvotes: 0

Related Questions