CREATE AN ORDERED COLUMN LIST FOR A QUALIFIED INSERT STATEMENT

If a table has an identity column that needs a row inserted because it
is missing a value within the identity value range, the insert statement
must be qualified. If, for example, the table has the identity values of
1,3, and 4 for three rows and it is missing the identity value of 2, you
must qualify the insert statement in order to insert the identity value of
2.

If the identity value should be the next largest value, it is not
necessary to qualify the insert. In this case, simply list the values for the
nonidentity columns in the correct order without qualifying the column
names. For example, in the previous example, it might instead be correct to
skip the identity value of 2 and insert another row without qualifying
the insert. The new row inserted will have the next identity value, 5.

In some databases, most tables have an identity column. On occasion, you
might find that a table is missing an identity value within a range of
identity values. If this is the case, the insert has to be a qualified
statement. Use the following query to obtain a qualified column list for an
insert statement.

SELECT name + ',' FROM syscolumns WHERE OBJECT_NAME(id) = 'x_temp' ORDER
BY colorder

The following is a simple script to demonstrate a qualified insert of a
missing row requiring an identity value.

SET NOCOUNT ON
GO
CREATE TABLE x_temp (key1 INT IDENTITY (1,1) NOT NULL,
col1 INT NULL, col2 INT NULL)
GO

INSERT x_temp VALUES (2,3)
INSERT x_temp VALUES (2,3)
INSERT x_temp VALUES (2,3)
INSERT x_temp VALUES (2,3)

DELETE x_temp WHERE key1 = 2

SELECT * FROM x_temp

SET IDENTITY_INSERT x_temp ON
GO
--Need to qualify the insert statement
INSERT x_temp (key1,col1,col2)
VALUES (2,2,3)
GO
SET IDENTITY_INSERT x_temp OFF
GO

SELECT * FROM x_temp
GO

----------------------------------------