将行插入到只有一个 IDENTITY 列的表中 [英] Inserting rows into a table with one IDENTITY column only

问题描述

我有一个只有一列的管理员表，adminId 是主键.由于业务规则，它必须是这样.

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.

我想一劳永逸地了解如何编写在这样的表中插入值的存储过程.我正在使用 SQL Server 和 T-SQL 并尝试使用 SCOPE_IDENTITY() 但这不起作用，因为该表将 INSERT_IDENTITY 设置为 false 或关闭.

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.

我真的不想为了能够插入新行而插入虚拟值.谢谢！

I'd really like to not insert a dummy value just to be able to insert a new row. Thanks!

推荐答案

如果您有一列是 IDENTITY，请执行此操作

If you have one column that is an IDENTITY, just do this

INSERT MyTable DEFAULT VALUES;  --allows no column list. The default will be the IDENTITY
SELECT SCOPE_IDENTITY();

如果你没有身份，那你可以设置吗?这是最好的方法..并使用上面的 SQL.

If you don't have identity, then can you set it? This is the best way.. and use the SQL above.

如果没有，你想插入一个新行

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

注意事项:

• 在高负载下，MAX 解决方案可能会因重复而失败
• SCOPE_IDENTITY 是之后事实，而不是之前
• SCOPE_IDENTITY 仅适用于 IDENTITY 列.使用 IDENT_CURRENT 重复任何白痴
• 输出子句替换了 MAX 解决方案的 SCOPE_IDENTITY

这篇关于将行插入到只有一个 IDENTITY 列的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！