|
|
Error Message
Server: Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO
statement, to table '<Table Name>', which already
has column '<Column Name>' that inherits the
identity property.
Causes
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
|
The structure of the new table is defined by the attributes of the expressions in the select list. When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT INTO was executed.
If a column in the source table is an IDENTITY column, the new table will inherit the IDENTITY property of the column when the new table is created. To illustrate, the following script will create a new table based on the [dbo].[Users] table and the IDENTITY property of the [UserID] column will be inherited by the new table:
CREATE TABLE [dbo].[Users] (
[UserID] INT IDENTITY(1, 1) NOT NULL,
[UserName] VARCHAR(50) NOT NULL,
[FirstName] VARCHAR(100) NOT NULL,
[LastName] VARCHAR(100) NOT NULL
)
GO
SELECT *
INTO [dbo].[Users2]
FROM [dbo].[Users]
GO
If a new column is to be defined as the IDENTITY column of the new table created by the SELECT INTO statement, the IDENTITY function can be used. The IDENTITY function is used only in a SELECT statement with an INTO <table> clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with the CREATE TABLE and ALTER TABLE statements.
The syntax of the IDENTITY function is as follows:
IDENTITY( <data_type> [, <seed>, <increment> ] ) AS <column_name>
The <data_type> parameter is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type. The <seed> parameter is the integer value to be assigned to the first row in the table.
Unfortunately, using the IDENTITY function with the SELECT INTO statement to create a new table where the source table already contains an IDENTITY column will generate an error, as can be seen from the following statements:
SELECT IDENTITY(INT, 1, 1) AS [NewID], *
INTO [dbo].[NewUsers]
FROM [dbo].[Users]
ORDER BY [UserName]
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table 'dbo.NewUsers',
which already has column 'UserID' that inherits the identity property.
Solution / Work Around:
This error can easily be overcome by removing the IDENTITY property of the column from the source table so that the new column created by the IDENTITY function can have the IDENTITY property of the new table. There are a couple of ways of removing the IDENTITY property of a column when used in the SELECT INTO statement.
The first method is by performing a mathematical function with the IDENTITY column, as can be seen from the following scripts:
SELECT IDENTITY(INT, 1, 1) AS [NewID], [UserID] * 1 AS [OldUserID],
[UserName], [FirstName], [LastName]
INTO [dbo].[NewUsers]
FROM [dbo].[Users]
SELECT IDENTITY(INT, 1, 1) AS [NewID], [UserID] + 0 AS [OldUserID],
[UserName], [FirstName], [LastName]
INTO [dbo].[NewUsers]
FROM [dbo].[Users]
The second method is by using the CAST or CONVERT function to convert the data type of the IDENTITY column into another data type. Actually, it doesn’t have to be another data type and it can be the same data type, as can be seen from the following statements:
SELECT IDENTITY(INT, 1, 1) AS [NewID], CAST([UserID] AS INT) AS [OldUserID],
[UserName], [FirstName], [LastName]
INTO [dbo].[NewUsers]
FROM [dbo].[Users]
SELECT IDENTITY(INT, 1, 1) AS [NewID], CONVERT(INT, [UserID]) AS [OldUserID],
[UserName], [FirstName], [LastName]
INTO [dbo].[NewUsers]
FROM [dbo].[Users]
Interestingly, if a new table is created using the SELECT INTO statement and two tables instead of just one are used as the source tables and both tables contain IDENTITY columns, this error is not generated. The IDENTITY property of the IDENTITY columns from both tables is not inherited by the new table. To illustrate, the following script creates a new table using the SELECT INTO statement wherein 2 tables are joined and both tables contain IDENTITY columns:
CREATE TABLE [dbo].[Customer] (
[CustomerID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
CREATE TABLE [dbo].[CustomerAddress] (
[CustomerAddressID] INT IDENTITY(1, 1) NOT NULL,
[CustomerID] INT NOT NULL REFERENCES [dbo].[Customer] ( [CustomerID] ),
[Address1] VARCHAR(100) NOT NULL,
[Address2] VARCHAR(100) NULL,
[City] VARCHAR(50) NOT NULL,
[State] CHAR(2) NOT NULL,
[ZIP] VARCHAR(10) NOT NULL
)
SELECT A.[CustomerID], A.[FirstName], A.[LastName], B.[CustomerAddressID],
B.[Address1], B.[Address2], B.[City], B.[State], B.[ZIP]
INTO [dbo].[AllCustomerAddresses]
FROM [dbo].[Customer] A INNER JOIN [dbo].[CustomerAddress] B
ON A.[CustomerID] = B.[CustomerID]
The resulting table will not inherit the IDENTITY property of the [CustomerID] and the [CustomerAddressID] and the new table will not have any IDENTITY column. Given this, the IDENTITY function can be used to generate an IDENTITY column for the new table.
SELECT IDENTITY(INT, 1, 1) AS [AllCompanyAddressesID], A.[CustomerID], A.[FirstName],
A.[LastName], B.[CustomerAddressID], B.[Address1], B.[Address2], B.[City], B.[State],
B.[ZIP]
INTO [dbo].[AllCompanyAddresses]
FROM [dbo].[Customer] A INNER JOIN [dbo].[CustomerAddress] B
ON A.[CustomerID] = B.[CustomerID]
GO
Given this scenario, an alternative to avoiding this error message (Msg 8108) is by performing a JOIN on the source table to create the new table when using the SELECT INTO statement. If there is only one table available and it cannot be joined with any another table, it can be joined to itself and still generate the desired outcome, as can be seen from the following:
SELECT IDENTITY(INT, 1, 1) AS [NewID], A.[UserID] AS [OldUserID], A.[UserName],
A.[FirstName], A.[LastName]
INTO [dbo].[NewUsers]
FROM [dbo].[Users] A INNER JOIN [dbo].[Users] B
ON A.[UserID] = B.[UserID]
|