SQL选择或插入返回ID

Question

这里有一个关于SQL的简短问题（使用sql-server-2008）。

我有一个映射表names，包含以下列：

ID DisplayName

我想要做的第一件事是执行以下代码：SELECT [ID] FROM [names] WHERE [DisplayName] = 'chuck';

但是，如果名为'chuck'的记录不存在于数据库中，我希望创建它，并返回自动递增的ID。

我想知道SQL是否有一些内置的简便方法来完成这个任务，还是我必须选择冗长的方式？

冗长的方式可能像这样：

SELECT COUNT(ID) AS count, ID FROM names WHERE DisplayName='chuck'
IF(count > 0)
    SELECT ID as ReturnID;
ELSE
    BEGIN
    INSERT INTO names(DisplayName) values('chuck');
    SELECT scope_identity() as ReturnID;
    END

我没有测试过最后那个语句，但我认为长方法应该是这样的。如果没有内置的方法，我希望有人可以简单地更正这个语句（因为我确定它不完全正确）。

- Kelly Elton

---

如果仅支持200*+，请使用合并语法。 - OMG Ponies

你能提供一个例子吗？我正在尝试学习更高级的使用SQL的方法。 - Kelly Elton

@kelton52：OMG Ponies链接的文章末尾有示例。你需要往下滚动一点。 - Andrew Savinykh

@zespri 我看了那些例子，但它们都没有做我想做的事情。我浏览了这些例子，但是并没有理解它们。此外，如果他把他的评论作为答案并附上一个例子，我就可以选择它了。 - Kelly Elton

@kelton52 这是最后一个使用 OUTPUT 的地方，但在你的情况下这不起作用。它仅适用于插入或更新场景。否则，当记录已经存在时，输出将为空。 - Jens Mühlenhoff

Answer 1

您也需要关注交易：

set XACT_ABORT on
begin tran

declare @ID int

select @ID = ID from names with (holdlock, updlock) WHERE DisplayName='chuck'

if @@rowcount = 0
begin
  INSERT INTO names(DisplayName) values('chuck');
  set @ID = scope_identity();
end

select @ID as ReturnID;

commit tran

注意使用表提示 - holdlock 和 updlock。它们可以防止另一个线程执行完全相同的查询并创建第二行。有关更多信息，请查找隔离、同步、死锁和并发更新。

- Coder

---

好的，运行了这个程序现在表格死锁了...有什么想法吗？ - Kelly Elton

你在那里两次使用了“from”，我假设这不是故意的。 - Kelly Elton

同样，SQL不识别xac_abort。 - Kelly Elton

基本上，这个查询每次都有效，但是对于表的其他查询则无效。 - Kelly Elton

请注意，此查询将从一个脚本运行，而不是网站或其他任何地方...因此不会同时调用此查询多次..实质上，这只是一个将CSV值插入数据库的Perl脚本。 - Kelly Elton

Answer 2

go
create table names
(
    Id int primary key identity(1,1),
    displayname varchar(100),
);

go
create procedure P1
    @displayname varchar(100)
as
    insert into names (displayname)
    select @displayname
    where not exists (
        select * from names, (select @displayname as displayname) as names2
            where names.displayname = names2.displayname);

    -- race condition is possible here,
    -- but in some cases you still may get away with this

    select id from names where displayname = @displayname;

go  
declare @dn varchar(100);

set @dn = 'chuck'; exec P1 @dn; exec P1 @dn; exec P1 @dn;

set @dn = 'buck'; exec P1 @dn; exec P1 @dn;

select * from names;

go
drop table names; drop procedure P1;

输出将为1, 1, 1, 2, 2，表格内容有两行。

- Yola

Answer 3

我会这样做：

IF (NOT EXISTS(SELECT null from names where DisplayName='Chuck'))
   INSERT INTO Names (DisplayName) Values ('Chuck')

SELECT ID as ReturnID FROM Names where DisplayName='Chuck'

虽然不会节省太多空间

- JamieB

---

这最多执行两个查询，最坏的情况下只执行一个查询，不是吗？ - crush