Insert into values ​​with where clause

Question

I am trying to programmatically enter values ​​into a table.

I can not use direct Select @variables. I have to use the Values ​​keyword.

How can I create a where clause when using values ​​in an insert.

I try 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) 

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

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

Answer 3

If you are trying to make sure that the MyID column does not contain duplicates, you have at least 3 options: 1) make the column unique (create an index in this column and declare it as unique or, even better, a primary key) 2) make the column increment automatically . That way, you don’t even need to assign values ​​to it. 4) you can use the solution of Joe Stefanelli (on this topic). He is a programmer friendly and allows you to assign any value you want.

Answer 4

Also, the Merge (UPSERT) option is a good option for a single run. in this example, the match is not populated, but you can add an agreed WHEN statement and timestamps or timestamps.

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