How to make Identity Column in Existing Table.

We got a request to make column IDENTITY column in the existing table.

This is the process we followed.

1. Script-out the  table and index structure.

2. Rename the table using SQL syntax

EXEC sp_rename 'Old_Table_Name', 'New_Table_Name'


3.Run the scripted SQL from old table in step-1 and & make the column  DB_ASC identity column before running statement

CREATE TABLE  [D_ASC](
[DBAS_ID] [varchar](30) NOT NULL,
[DBC_AS_ID] [varchar](10) NOT NULL,
[DB_ASC ] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [X_AS_DB] PRIMARY KEY CLUSTERED
(
[DB_ASC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DB_Data]
) ON [DB_Data]
 GO

4. Run the  Import/Export wizard.

Right click on database where we have New table ----> Tasks ----> Import

5. Data Source will be the server/database which have old table

 Connect to source server and DB name where Old table exists. Click Next

6. Choose Destination Server/DB name  and Click on Next button

 7. select first option and click on next button.

8. Choose source and destination tables & Click on "Edit Mapping" Button.
On edit mapping window, Check the "Enable Identity insert" Box else we will get error message while data transfer from source table to destination.

9. Now click Next - Next and Finish Button. data will be transferred to new table with Identity column values.

• Tweet
• Share
• Share
• Share
• Share