SQL 将行插入到只有一个 IDENTITY 列的表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4117676/
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
Inserting rows into a table with one IDENTITY column only
提问by Phil
I have a table Administrator with only one column, adminId which is the primary-key. Because of business rules it has to be this way.
我有一个只有一列的管理员表，adminId 是主键。由于业务规则，它必须是这种方式。
I'd like to understand once and for all how I can write stored procedures that insert values in tables like this. I am using SQL Server and T-SQL and tried using SCOPE_IDENTITY() but that doesn't work since the table has INSERT_IDENTITY to false or off.
我想一劳永逸地了解如何编写在这样的表中插入值的存储过程。我正在使用 SQL Server 和 T-SQL 并尝试使用 SCOPE_IDENTITY() 但这不起作用，因为表的 INSERT_IDENTITY 为 false 或 off。
I'd really like to not insert a dummy value just to be able to insert a new row. Thanks!
我真的不想插入一个虚拟值只是为了能够插入一个新行。谢谢！
回答by gbn
If you have one column that is an IDENTITY, just do this
如果您有一列是 IDENTITY，请执行此操作
INSERT MyTable DEFAULT VALUES; --allows no column list. The default will be the IDENTITY
SELECT SCOPE_IDENTITY();
If you don't have identity, then can you set it? This is the best way.. and use the SQL above.
如果你没有身份，那你可以设置吗？这是最好的方法……并使用上面的 SQL。
If not, you want to insert a new row
如果没有，你想插入一个新行
INSERT MyTable (admidid)
OUTPUT INSERTED.admidid --returns result to caller
SELECT ISNULL(MAX(admidid), 0) + 1 FROM MyTable
Notes:
笔记：
- Under high loads the MAX solution may fail with duplicates
- SCOPE_IDENTITY is afterthe fact, not before
- SCOPE_IDENTITY only works with an IDENTITY column. Ditto any idiocy using IDENT_CURRENT
- The output clause replaces SCOPE_IDENTITY for the MAX solution
- 在高负载下，MAX 解决方案可能会因重复而失败
- SCOPE_IDENTITY 是在事实之后，而不是之前
- SCOPE_IDENTITY 仅适用于 IDENTITY 列。使用 IDENT_CURRENT 同上任何白痴
- 输出子句替换 SCOPE_IDENTITY 为 MAX 解决方案
回答by DataWriter
You need to add the IDENTITY_INSERT to your select statement:
您需要将 IDENTITY_INSERT 添加到您的选择语句中：
SET IDENTITY_INSERT MyTable ON
INSERT INTO MyTable
(AdminCol)
SELECT AdminColValue
FROM Tableb
When you're done, make sure you remember to
完成后，请确保您记得
SET IDENTITY_INSERT MyTable OFF
Here's a good description of how it works from BOL: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx
这里有一个很好的描述它是如何从 BOL 工作的：http: //msdn.microsoft.com/en-us/library/aa259221(SQL.80) .aspx
回答by Tim
@Phil: Don't you mean your table has two(2) columns, the autoincrementing PK column and an AdminName column? If it only has one column where the AdminName goes, the AdminName is the PK and you cannot autoincrement a string, of course. Do the business rules expect you to make a fully-qualified Windows username the primary key? That would be viable and make sense, because then you wouldn't need an alternate unique index on the AdminName column.
@Phil：你不是说你的表有两（2）列，自动递增的 PK 列和一个 AdminName 列？如果它只有一列 AdminName 所在的列，则 AdminName 是 PK，当然您不能自动递增字符串。业务规则是否希望您将完全限定的 Windows 用户名设为主键？这将是可行且有意义的，因为这样您就不需要 AdminName 列上的备用唯一索引。
But if your table has two columns, not one:
但是，如果您的表有两列，而不是一列：
In SQLServer the autoincrement is part of the table/column definition. You define the column as an integer and then also make it an identity column, specifying the increment, usually 1, but it could be 2 or 5 or 10 or whatever. To insert a row, you simply insert the other column(s) value(s) and do nothing with the PK column:
在 SQLServer 中，自动增量是表/列定义的一部分。您将列定义为整数，然后将其设为标识列，指定增量，通常为 1，但也可以是 2 或 5 或 10 或其他。要插入一行，您只需插入其他列的值，而不对 PK 列执行任何操作：
insert into T
(foo) -- column(s) list
values('bar') -- values list
Your stored proc that does the insert can make SCOPE_IDENTITY a RETURN value or SCOPE_IDENTITY can be passed back to the client as an OUT parameter.
执行插入的存储过程可以使 SCOPE_IDENTITY 成为 RETURN 值，或者 SCOPE_IDENTITY 可以作为 OUT 参数传递回客户端。
P.S. SCOPE_IDENTITY() returns the most recently generated autoincremented identity value in the current scope; it does not generate the next identity value.
PS SCOPE_IDENTITY() 返回当前范围内最近生成的自增标识值；它不会生成下一个标识值。
EDIT:
编辑：
Presumably, your Administrators table contains a set of administrators. But if it has no columns whatsoever other than the integer primary key column, there is no way to identify the administators; the only thing you can do is distinguish them from each other. That doesn't get you very far at all. But if your Administrator table had either of the following structures:
据推测，您的管理员表包含一组管理员。但是如果它除了整数主键列之外没有任何列，则无法识别管理员；您唯一能做的就是将它们区分开来。这根本不会让你走得很远。但是，如果您的管理员表具有以下结构之一：
ID INTEGER PRIMARY KEY AUTOINCREMENT
windowsusername varchar(50) (unique index)
OR
或者
windowsusername varchar(50) primary key
you would be able to reference the Administrator's table from other tables, and the foreign keys would be MEANINGFUL. And that's precisely what a table consisting of a single integer column lacks -- meaning.
您将能够从其他表中引用管理员的表，并且外键将是有意义的。而这正是由单个整数列组成的表所缺乏的——意义。
Having two columns, you could then have a stored procedure do this:
有两列，然后你可以有一个存储过程来做到这一点：
insert into Administrators
(windowsusername)
values('mydomain\someusername');
return SCOPE_IDENTITY();
and your client-program would get back as a return value the autoincremented id that had been autogenerated and assigned to the newly inserted row. This approach is the usual practice, and I would go so far as to say that it is considered "best practice".
并且您的客户端程序将作为返回值返回已自动生成并分配给新插入行的自动递增 ID。这种方法是通常的做法，我什至会说它被认为是“最佳做法”。
P.S. You mention that you didn't know how to "insert a value" if you "didn't have anything to insert". There's a contradiction there. If you have nothing to insert, why insert? Why would you create, say, a new CUSTOMER record if you know absolutely nothing about the customer? Not their name, their city, their phone number, nothing?
PS 你提到如果你“没有任何东西要插入”，你不知道如何“插入一个值”。这里面有矛盾。如果你没有什么可插入的，为什么要插入？如果您对客户一无所知，为什么要创建新的 CUSTOMER 记录？不是他们的名字，他们的城市，他们的电话号码，什么都没有？
