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.
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'
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments