Specify "NEXT VALUE" for INSERT statement using identity column in SQL Server

Question

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

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.

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?

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

I'm not sure you can do that. Is this framework a popular thing? Are you sure that it has that restriction? — DavidG
– DavidG, Commented May 21, 2016 at 22:41
The framework is not open source, and is not fixable in this manner. — John Fowler
– John Fowler, Commented May 22, 2016 at 1:12
your framework might have an option to use store procedure for insert, i belive it will be easier way to go that road. — vittore
– vittore, Commented May 22, 2016 at 4:11

Code Different · Accepted Answer · 2016-05-22 04:12:48Z

11

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.

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'

edited May 22, 2016 at 4:12

answered May 22, 2016 at 4:06

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vamsi · Accepted Answer · 2016-05-22 01:30:14Z

1

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

answered May 22, 2016 at 1:30

vamsi

3521 gold badge3 silver badges11 bronze badges

1 Comment

John Fowler Over a year ago

Yes, but I want the identity column to generate the ID. I don't want to supply it.

Alex Kudryashev · Accepted Answer · 2016-05-22 02:43:21Z

1

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

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

answered May 22, 2016 at 2:43

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

2 Comments

TomTom Over a year ago

Actually you can. SET IDENTITY INSERT ON

Alex Kudryashev Over a year ago

@TomTom did I say something opposite? I showed how and when you can use and override identity constraint.

DavidG · Accepted Answer · 2016-05-21 22:45:00Z

0

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

Fix the library if possible.

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')

answered May 21, 2016 at 22:45

DavidG

120k13 gold badges232 silver badges239 bronze badges

Collectives™ on Stack Overflow

Specify "NEXT VALUE" for INSERT statement using identity column in SQL Server

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related