0

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)
Improve this question
6
  • 1
    Are you sure you're not missing a where clause in the 2nd select? Commented May 11, 2012 at 18:42
  • I should not have a where in the second select because I am choosing all of them. Commented May 11, 2012 at 18:43
  • So you only want to insert if the table is empty? Commented May 11, 2012 at 18:44
  • no, I only want to insert unique values Commented May 11, 2012 at 18:44
  • 4
    Your second select "where not exists( select user_id,real_name from targetTable)" is checking if it finds anything in that table.. so you might wanna put a where clause to check if the values you're trying to insert do not exist in that table.. Commented May 11, 2012 at 18:46

4 Answers 4

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

0 <= (SELECT COUNT(*) ...) ? Also, this solution is not safe in a multiuser enviroment.
This will always be true > 0 , if there are duplicates
accidentally typed <= instead of >=
In this case it will always be just 1 user. So that is fine
but isn't this exactly to avoid duplicates??? I'm sorry.. I don't know what you're asking anymore
|
0

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

Improve this answer

Comments

0

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.

Improve this answer

5 Comments

You don't think your sub-select is a separate query? It is. If you want a single query version I will post one below
@BrianWhite - It was my impression that it was effectively wrapped in a transaction with the INSERT. Why do I get the feeling that I'm about to learn another unsettling lesson about SQL Server?
You might be right :) The version I have below was what I came up with to kill off a race condition problem I had years ago, but I don't think I tried a version like yours.
I think that you are right as long as the connection is READ COMMITTED or higher. You'd only have the potential for trouble if you used read uncommitted or added a with(nolock) on the sub-select. Good discussion here, but it only really applies for updates: sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/27/…
@BrianWhite - There are two ways I know of to make a single SQL statement misbehave: either fiddle with the isolation level to allow multiple cooks in the kitchen, or mix data from databases that do not support distributed transactions, e.g. via OPENDATASOURCE(). In this case I'm still thinking that the INSERT is one statement regardless of the complexity. It could JOIN a dozen tables back on themselves, have various subqueries and a funky OUTPUT clause, but it remains one statement. Now imagine the MERGE statement from heck ... .
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.