insert into values with where clause

Question

I am trying to programmatically enter values into my table.

I cannot use a straight Select @variables. I have to use the keyword Values.

How can I create a where clause when using Values in the insert into.

I am trying to avoid duplicates

  DECLARE @MyID INT
  DECLARE @Phone varchar(10)
  DECLARE @MyDATE DateTime
  DECLARE @Agent as varchar(50)
  DECLARE @Charge as varchar(50)
  DECLARE @Vendor as varchar(50)

  SET @MyID = 215199999
  SET @Phone = '9999999999'
  SET @MyDATE = '2010-12-04 11:56:12.000'
  SET @Agent = 'fbrown'
  SET @Charge = 'NO'
  SET @Vendor = 'NO'

  INSERT INTO [MyDB].[dbo].[Accounts]
  (MyID,Phone,MyDate,Agent,Charge,Vendor)
  VALUES (
  @MyID
  ,@Phone
  ,@MyDATE
  ,@Agent
  ,@Charge
  ,@Vendor 
  ) WHERE MyID NOT IN (@MyID)

Why would you use a where clause on an insert? Are you actually trying to insert non-duplicates? — Constantin Baciu, Feb 06 '12 at 19:42
It's not a concept. If you are selecting data from another query, a `WHERE` clause makes sense on the data source. — HABO, Feb 06 '12 at 19:44

score 24 · Accepted Answer · answered Feb 06 '12 at 19:43

24

IF NOT EXISTS(SELECT 1 FROM [MyDB].[dbo].[Accounts] WHERE MyID = @MyID)
    INSERT INTO [MyDB].[dbo].[Accounts]
        (MyID, Phone, MyDate, Agent, Charge, Vendor)
        VALUES 
        (@MyID, @Phone, @MyDATE, @Agent, @Charge, @Vendor)

answered Feb 06 '12 at 19:43

Joe Stefanelli

132,803
19
237
235

score 3 · Answer 2 · answered Feb 06 '12 at 19:49

3

Try using

if not exists ( select top 1 * from [MyDB].[dbo].[Accounts] Where MyID = @MyID )
INSERT INTO [MyDB].[dbo].[Accounts]
  (MyID,Phone,MyDate,Agent,Charge,Vendor)
  VALUES (
  @MyID
  ,@Phone
  ,@MyDATE
  ,@Agent
  ,@Charge
  ,@Vendor 
  )

answered Feb 06 '12 at 19:49

Siva Charan

17,940
9
60
95

If really I want to delete my post, just update me so i can delete my post – Siva Charan Feb 06 '12 at 19:53

score 1 · Answer 3 · answered Jul 30 '16 at 16:18

1

Also the Merge (UPSERT) option is a good option for a single execute. in this example the when matched is not filled, but you could add the WHEN matched statement and update timestamps or counters.

 MERGE
   Accounts AS target
USING
(select  @MyID as myID ) AS source
ON
   target.myID = source.myID 

WHEN NOT MATCHED THEN
INSERT (MyID,Phone,MyDate,Agent,Charge,Vendor)
  VALUES (
  @MyID
  ,@Phone
  ,@MyDATE
  ,@Agent
  ,@Charge
  ,@Vendor 
  );

answered Jul 30 '16 at 16:18

langeleppel

121
2
3

P.s. This topic is 4,5 years old. :-) – Tenzin Jul 30 '16 at 17:35
4

@Tenzin So what? It doesn't mean that people don't look for answers for the same question anymore – DixonD Nov 03 '16 at 14:41
Can confirm, people are still looking. – maedox Feb 20 '20 at 09:27

score 1 · Answer 4 · answered Feb 06 '12 at 19:46

If you are trying to make sure that the MyID column doesn't contain any duplicates, you have at least 3 choices: 1) make the column unique (create an index on that column and declare it as unique, or, better yet, a primary key) 2) make the column auto-increment. This way, you don't even need to assign values to it. 4) you can use Joe Stefanelli's solution (on this thread). It's programmer friendly and alows you to assign any value you want.

MikeTeeVee · Answer 5 · 2021-03-04T23:06:55.923

As a rule of thumb, I prefer to avoid Branching Logic (using IF) whenever possible.
This is because the Query Plan is Cached for future runs based on the 1st-Run it happened to use
(which is only one scenario of possible branches/routes you could take).

To avoid this, I include Where-Clauses in all DML Statements (when applicable).
This ensure all possible DML Operations are included in the Query Plan.
If I find this isn't possible (or too over-complicated), then that's the code-smell I use to determine if I should create a new Sproc to Branch my logic into.

With that out of the way, this is how you may use a Where-Clause for your Insert-Statement:

INSERT [MyDB].[dbo].[Accounts]
       (MyID, Phone, MyDate, Agent, Charge, Vendor)
SELECT @MyID,@Phone,@MyDate,@Agent,@Charge,@Vendor
 WHERE NOT EXISTS (SELECT * FROM [MyDB].[dbo].[Accounts] WHERE MyID = @MyID)

Side Note: Avoid T-SQL's MERGE (Upsert) at all cost. In my experience it is best to write out your Inserts and Updates separately and to always Update before Insert. This avoids potential random/intermittent Deadlocks when Updating a Newly Inserted Record (that is still temporarily Locked after being freshly minted).

insert into values with where clause

5 Answers5

Linked