SQL 如果存在，则选择 ELSE INSERT 然后选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1488355/
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
IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT
提问by Phillip Senn
How do you say the following in Microsoft SQL Server 2005:
你如何在 Microsoft SQL Server 2005 中表达以下内容：
IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN
SELECT TableID FROM Table WHERE FieldValue=''
ELSE
INSERT INTO TABLE(FieldValue) VALUES('')
SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END IF
What I'm trying to do is to see if there is a blank fieldvalue already, and if there is then return that TableID, else insert a blank fieldvalue and return the corresponding primary key.
我想要做的是查看是否已经有一个空白字段值，如果有则返回该 TableID，否则插入一个空白字段值并返回相应的主键。
回答by zvolkov
You need to do this in transaction to ensure two simultaneous clients won't insert same fieldValue twice:
您需要在事务中执行此操作以确保两个并发客户端不会两次插入相同的 fieldValue：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
DECLARE @id AS INT
SELECT @id = tableId FROM table WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
INSERT INTO table (fieldValue) VALUES (@newValue)
SELECT @id = SCOPE_IDENTITY()
END
SELECT @id
COMMIT TRANSACTION
you can also use Double-checked lockingto reduce locking overhead
您还可以使用双重检查锁定来减少锁定开销
DECLARE @id AS INT
SELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = tableID FROM table WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
INSERT INTO table (fieldValue) VALUES (@newValue)
SELECT @id = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
END
SELECT @id
As for why ISOLATION LEVEL SERIALIZABLE is necessary, when you are inside a serializable transaction, the first SELECT that hits the table creates a range lock covering the place where the record should be, so nobody else can insert the same record until this transaction ends.
至于为什么 ISOLATION LEVEL SERIALIZABLE 是必要的，当你在一个可序列化的事务中时，命中表的第一个 SELECT 会创建一个范围锁，覆盖记录应该在的地方，所以在这个事务结束之前没有人可以插入相同的记录。
Without ISOLATION LEVEL SERIALIZABLE, the default isolation level (READ COMMITTED) would not lock the table at read time, so between SELECT and UPDATE, somebody would still be able to insert. Transactions with READ COMMITTED isolation level do not cause SELECT to lock. Transactions with REPEATABLE READS lock the record (if found) but not the gap.
如果没有 ISOLATION LEVEL SERIALIZABLE，默认的隔离级别 (READ COMMITTED) 不会在读取时锁定表，因此在 SELECT 和 UPDATE 之间，仍然有人能够插入。具有 READ COMMITTED 隔离级别的事务不会导致 SELECT 锁定。具有 REPEATABLE READS 的事务锁定记录（如果找到）但不锁定间隙。
回答by Jane
IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='')
BEGIN
SELECT TableID FROM Table WHERE FieldValue=''
END
ELSE
BEGIN
INSERT INTO TABLE(FieldValue) VALUES('')
SELECT SCOPE_IDENTITY() AS TableID
END
See herefor more information on IF ELSE
有关IF ELSE 的更多信息，请参见此处
Note: written without a SQL Server install handy to double check this but I think it is correct
注意：在没有安装 SQL Server 的情况下编写，可以方便地仔细检查，但我认为它是正确的
Also, I've changed the EXISTS bit to do SELECT 1 rather than SELECT * as you don't care what is returned within an EXISTS, as long as something is I've also changed the SCOPE_IDENTITY() bit to return just the identity assuming that TableID is the identity column
此外，我已将 EXISTS 位更改为 SELECT 1 而不是 SELECT * 因为您不关心 EXISTS 中返回的内容，只要有什么我也更改了 SCOPE_IDENTITY() 位以仅返回身份假设 TableID 是标识列
回答by David
You were close:
你很接近：
IF EXISTS (SELECT * FROM Table WHERE FieldValue='')
SELECT TableID FROM Table WHERE FieldValue=''
ELSE
BEGIN
INSERT INTO TABLE (FieldValue) VALUES ('')
SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END
回答by Guffa
You just have to change the structure of the if...else..endifsomewhat:
你只需要if...else..endif稍微改变一下结构：
if exists(select * from Table where FieldValue='') then begin
select TableID from Table where FieldValue=''
end else begin
insert into Table (FieldValue) values ('')
select TableID from Table where TableID = scope_identity()
end
You could also do:
你也可以这样做：
if not exists(select * from Table where FieldValue='') then begin
insert into Table (FieldValue) values ('')
end
select TableID from Table where FieldValue=''
Or:
或者：
if exists(select * from Table where FieldValue='') then begin
select TableID from Table where FieldValue=''
end else begin
insert into Table (FieldValue) values ('')
select scope_identity() as TableID
end
回答by onedaywhen
It sounds like your table has no key. You should be able to simply try the INSERT: if it's a duplicate then the key constraint will bite and the INSERTwill fail. No worries: you just need to ensure the application doesn't see/ignores the error. When you say 'primary key' you presumably mean IDENTITYvalue. That's all very well but you also need a key constraint (e.g. UNIQUE) on your natural key.
听起来你的桌子没有钥匙。您应该能够简单地尝试INSERT: 如果它是重复的，那么关键约束将被咬住并且INSERT将失败。不用担心：您只需要确保应用程序不会看到/忽略错误。当您说“主键”时，您大概是指IDENTITY价值。这一切都很好，但您还需要UNIQUE对自然键进行键约束（例如）。
Also, I wonder whether your procedure is doing too much. Consider having separate procedures for 'create' and 'read' actions respectively.
另外，我想知道您的程序是否做得太多。考虑分别为“创建”和“读取”操作设置单独的程序。
回答by Phillip Senn
create schema tableName authorization dbo
go
IF OBJECT_ID ('tableName.put_fieldValue', 'P' ) IS NOT NULL
drop proc tableName.put_fieldValue
go
create proc tableName.put_fieldValue(@fieldValue int) as
declare @tableid int = 0
select @tableid = tableid from table where fieldValue=''
if @tableid = 0 begin
insert into table(fieldValue) values('')
select @tableid = scope_identity()
end
return @tableid
go
declare @tablid int = 0
exec @tableid = tableName.put_fieldValue('')
回答by Rob Garrison
DECLARE @t1 TABLE (
TableID int IDENTITY,
FieldValue varchar(20)
)
--<< No empty string
IF EXISTS (
SELECT *
FROM @t1
WHERE FieldValue = ''
) BEGIN
SELECT TableID
FROM @t1
WHERE FieldValue=''
END
ELSE BEGIN
INSERT INTO @t1 (FieldValue) VALUES ('')
SELECT SCOPE_IDENTITY() AS TableID
END
--<< A record with an empty string already exists
IF EXISTS (
SELECT *
FROM @t1
WHERE FieldValue = ''
) BEGIN
SELECT TableID
FROM @t1
WHERE FieldValue=''
END
ELSE BEGIN
INSERT INTO @t1 (FieldValue) VALUES ('')
SELECT SCOPE_IDENTITY() AS TableID
END
