SQL 使用 where 子句插入值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9166157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert into values with where clause
提问by Internet Engineer
I am trying to programmatically enter values into my table.
我正在尝试以编程方式将值输入到我的表中。
I cannot use a straight Select @variables. I have to use the keyword Values.
我不能直接使用 Select @variables。我必须使用关键字 Values。
How can I create a where clause when using Values in the insert into.
在插入中使用值时如何创建 where 子句。
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)
回答by Joe Stefanelli
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)
回答by Siva Charan
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
)
回答by langeleppel
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.
此外，合并 (UPSERT) 选项是单次执行的不错选择。在此示例中，未填充 when 匹配，但您可以添加 WHEN 匹配语句并更新时间戳或计数器。
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
);
回答by Constantin Baciu
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.
如果您试图确保 MyID 列不包含任何重复项，您至少有 3 个选择：1) 使列唯一（在该列上创建一个索引并将其声明为唯一的，或者更好的是，一个主键) 2) 使列自动递增。这样，您甚至不需要为其分配值。4）您可以使用Joe Stefanelli的解决方案（在此线程上）。它对程序员很友好，允许你分配任何你想要的值。
