SQL SERVER GYAN: Make Non identity column to an identity column in SQL Server

At times, we run into a situation where we have to convert an existing non identity column to an identity column. Let us assume that currently there is logic in place to generate running numbers for the primary key column of the table. We want to convert it to an identity column in order to get database generated value for the primary key and thus avoiding an extra logic to generate running number. In this blog post, we will show you how we can achieve it. Following is the table structure to demonstrate the example. Create the table structure and populate it with data.

CREATE TABLE dbo.Invoice
(
INVOICE_NUMBER INT NOT NULL,
INVOICE_DATE DATETIME NOT NULL,
CLIENT_ID INT NOT NULL,
INVOICE_AMT NUMERIC(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG TINYINT DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
– FileGroup clause
;

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(1,getdate(),101,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(2,getDate(),102,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(4,getdate(),103,1100.00);
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(6,getdate(),104,1100.00);

This is how data looks like. It is formatted for better viewing.

Inv# Invoice_Date Client_ID Invoice_Amount Paid_Flag
—- ———— ———- ————– ———
1 2007-02-18 15:25:09.107 101 1100.00 0
2 2007-02-18 15:25:09.107 102 1100.00 0
4 2007-02-18 15:25:09.107 103 1100.00 0
6 2007-02-18 15:25:09.107 104 1100.00 0

Unlike Oracle Sequences, which are separate objects, identity property is attached with the table. Table can contain only one identity column. There is no straight way to alter column to identity column or vice versa (altering identity column to regular column). We need to drop and re-create the column with identity property. This is how we will do it.

Since the invoice_number column is the primary key, we need to drop the primary key constraint first and then drop the column. Also the assumption is that this is a standalone table and doesn’t have any child tables. If there are child tables, then the FK constraints will need to be dropped, updates will need to be made to those child tables as well to update the references to the parent table using a temp table or a table variable to do the co-relation between the old and the new values and then the FK constraints will need to be re-created.

But before dropping the column, we need to make sure that we don’t loose existing data. So we will first save existing data into other table temporarily.

SELECT * INTO TEMP_INVOICE FROM INVOICE
GO

Successful execution of above command will make sure that existing data is saved safely into newly created TEMP_INVOICE table.
Now first we will truncate the data from existing table and we will drop constraint, column and re-create the column with identity property and then add the constraint back.

TRUNCATE TABLE INVOICE
GO
ALTER TABLE INVOICE DROP CONSTRAINT PK_INVOICE
GO
ALTER TABLE INVOICE DROP COLUMN INVOICE_NUMBER
GO

Now we will add column back with identity property attached to it.

ALTER TABLE INVOICE ADD INVOICE_NUMBER INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE INVOICE ADD CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_NUMBER)
GO

Now we will insert data back into INVOICE table from the TEMP_INVOICE. Since we want to retain the existing values, we will have to turn on the IDENTITY_INSERT property on as INVOICE_NUMBER column is created as an identity column.

SET IDENTITY_INSERT INVOICE ON
GO
INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
SELECT Invoice_Number, Invoice_date, client_ID, Invoice_Amt
FROM Temp_Invoice
GO
SET IDENTITY_INSERT Invoice OFF
GO

Following is the output once we inserted data back into INVOICE table. Output shows that we have retained the existing values of invoice number.

You can add few more records by omitting the invoice_number in the insert statement and see the output. Look for last two records.

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getdate(),105,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate(),106,1100.00);

After adding two more rows, following is the output.

Last step will be to drop the newly created table which is no longer required as we have successfully put the data back into INVOICE table.

DROP TABLE TEMP_INVOICE
GO

SQL SERVER GYAN

Wednesday, April 22, 2009

Make Non identity column to an identity column in SQL Server

No comments:

About Me