使用SQL Server中的标识列为INSERT语句指定“NEXT VALUE”

时间:2020-12-30 07:56:56

Consider the following table and SQL from Microsoft's INSERT documentation that deals with IDENTITY columns:

请考虑下面的表和Microsoft处理IDENTITY列的INSERT文档中的SQL:

CREATE TABLE dbo.T1 (column_1 int IDENTITY, column_2 VARCHAR(30));
GO

INSERT T1 (column_2) VALUES ('Row #2');

The INSERT statement does not specify column_1 as a column of the table, and SQL Server auto-populates the next value for that identity column. This is the normal way identity columns are handled.

INSERT语句未将column_1指定为表的列,SQL Server会自动填充该标识列的下一个值。这是处理标识列的常规方式。

How can I have the same behavior, while also specifying the column name?

如何在指定列名的同时具有相同的行为?

For example, I'm looking for something like:

例如,我正在寻找类似的东西:

INSERT INTO T1 (column_1, column_2) 
VALUES (NEXT VALUE, 'Row #3');
GO

I don't believe NEXT VALUE works here, but is there something that does work? Is there a key token or function that will indicate that the identity column should be used?

我不相信NEXT VALUE在这里工作,但是有什么能起作用吗?是否有一个密钥令牌或函数表明应该使用标识列?

Note: the reason I ask is that the framework I'm using requires all columns to be specified in the column list.

注意:我问的原因是我正在使用的框架要求在列列表中指定所有列。

4 个解决方案

#1


2  

If you are on SQL Server 2012 and later, you can use sequence. But you must remove the IDENTITY property from Column1 first. This can only be done by copy-and-rename a new table.

如果您使用的是SQL Server 2012及更高版本,则可以使用序列。但您必须首先从Column1中删除IDENTITY属性。这只能通过复制和重命名新表来完成。

CREATE SEQUENCE Column1_Sequence
    AS int
    START WITH 0;

CREATE TABLE T1
(
    Column1     int DEFAULT (NEXT VALUE FOR Column1_Sequence) PRIMARY KEY
,   Column2     nvarchar(30)
)

After that, you can insert data into the table in 2 ways:

之后,您可以通过两种方式将数据插入表中:

INSERT INTO T1 (Column1, Column2)
    SELECT      NEXT VALUE FOR Column1_Sequence
            ,   'Row #2'

INSERT INTO T1 (Column2)
    SELECT      'Hello world'

#2


1  

Can you set the identity insert on before inserting and then set the identity insert off

您可以在插入之前设置标识插入,然后设置标识插入

#3


1  

You cannot set value for identity column unless you set identity_insert on for this table (one at time). Some examples:

除非为此表设置identity_insert(一次一个),否则不能为identity列设置值。一些例子:

create table #tmp (id int identity(1,1), name varchar(10))

insert #tmp (id,name) values (2,'test')
--error Cannot insert explicit value for identity column in table '#tmp

set identity_insert #tmp on --for one table in DB
insert #tmp (id,name) values (2,'qwas')
select * from #tmp
set identity_insert #tmp off -- good practice
--works

--see current identity value
SELECT IDENT_CURRENT ('#tmp') AS Current_Identity;

--Reset identity value
DBCC CHECKIDENT (#tmp, RESEED, 999)
--next insert will be 1000

Of course, if you reset next identity to a value which conflicts with PK (common usage of identity) you will have Violation of PRIMARY KEY constraint error

当然,如果将下一个标识重置为与PK冲突的值(标识的常见用法),则会出现违反PRIMARY KEY约束错误

#4


0  

I am pretty sure there is no way to do that with SQL Server. Two workarounds that I can think of:

我很确定没有办法用SQL Server做到这一点。我能想到的两种解决方法:

  1. Fix the library if possible.
  2. 尽可能修复库。
  3. If the library supports it, you can create a view and INSERT into that instead. For example:

    如果库支持它,您可以创建一个视图并将其插入到该视图中。例如:

    CREATE TABLE MyTable
    (
        ID INT IDENTITY(1, 1),
        SomeColumn VARCHAR(100)
    )
    
    GO
    CREATE VIEW MyTableView
    AS
    SELECT SomeColumn
    FROM MyTable
    
    GO
    INSERT INTO MyTableView (SomeColumn) VALUES ('Test')
    

#1


2  

If you are on SQL Server 2012 and later, you can use sequence. But you must remove the IDENTITY property from Column1 first. This can only be done by copy-and-rename a new table.

如果您使用的是SQL Server 2012及更高版本,则可以使用序列。但您必须首先从Column1中删除IDENTITY属性。这只能通过复制和重命名新表来完成。

CREATE SEQUENCE Column1_Sequence
    AS int
    START WITH 0;

CREATE TABLE T1
(
    Column1     int DEFAULT (NEXT VALUE FOR Column1_Sequence) PRIMARY KEY
,   Column2     nvarchar(30)
)

After that, you can insert data into the table in 2 ways:

之后,您可以通过两种方式将数据插入表中:

INSERT INTO T1 (Column1, Column2)
    SELECT      NEXT VALUE FOR Column1_Sequence
            ,   'Row #2'

INSERT INTO T1 (Column2)
    SELECT      'Hello world'

#2


1  

Can you set the identity insert on before inserting and then set the identity insert off

您可以在插入之前设置标识插入,然后设置标识插入

#3


1  

You cannot set value for identity column unless you set identity_insert on for this table (one at time). Some examples:

除非为此表设置identity_insert(一次一个),否则不能为identity列设置值。一些例子:

create table #tmp (id int identity(1,1), name varchar(10))

insert #tmp (id,name) values (2,'test')
--error Cannot insert explicit value for identity column in table '#tmp

set identity_insert #tmp on --for one table in DB
insert #tmp (id,name) values (2,'qwas')
select * from #tmp
set identity_insert #tmp off -- good practice
--works

--see current identity value
SELECT IDENT_CURRENT ('#tmp') AS Current_Identity;

--Reset identity value
DBCC CHECKIDENT (#tmp, RESEED, 999)
--next insert will be 1000

Of course, if you reset next identity to a value which conflicts with PK (common usage of identity) you will have Violation of PRIMARY KEY constraint error

当然,如果将下一个标识重置为与PK冲突的值(标识的常见用法),则会出现违反PRIMARY KEY约束错误

#4


0  

I am pretty sure there is no way to do that with SQL Server. Two workarounds that I can think of:

我很确定没有办法用SQL Server做到这一点。我能想到的两种解决方法:

  1. Fix the library if possible.
  2. 尽可能修复库。
  3. If the library supports it, you can create a view and INSERT into that instead. For example:

    如果库支持它,您可以创建一个视图并将其插入到该视图中。例如:

    CREATE TABLE MyTable
    (
        ID INT IDENTITY(1, 1),
        SomeColumn VARCHAR(100)
    )
    
    GO
    CREATE VIEW MyTableView
    AS
    SELECT SomeColumn
    FROM MyTable
    
    GO
    INSERT INTO MyTableView (SomeColumn) VALUES ('Test')