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)

Solution 1

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)

Solution 2

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 
  )

Solution 3

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.

Solution 4

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

View more solutions

Comments

[Siva Charan]

If really I want to delete my post, just update me so i can delete my post

[Tenzin]

P.s. This topic is 4,5 years old. :-)

[DixonD]

@Tenzin So what? It doesn't mean that people don't look for answers for the same question anymore

[maedox]

Can confirm, people are still looking.