insert into values with where clause

问题:

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)

回答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)

回答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 
  )

回答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.

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