Systems Engineering and RDBMS

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.

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

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.

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
7     2007-02-18 15:43:06.473       105         1100.00           0
8     2007-02-18 15:43:06.473       106         1100.00           0

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