Home > SQL > Create identity to create table with data

Create identity to create table with data

Normally we can’t create the identity in the existing column, it must be created while creating the table. Suppose if you have a data in the table you want to add the identity in the table, we can do it in indirect way as below.

Consider the table as table1 and it contain a column as column1

Now am rename the table1 as table1_new as below

sp_rename ‘table1′,’table1_new’

Now create a new table as table1

create table table1(column1 int primary key identity(1,1))

then turn off identity

set identity_insert table1 on

Now copy all data from old table to new table as below

insert into table1(column1 )
select column1 from table1


Now turnon the identity

set identity_insert table1 off

Find the max value of column

select MAX(column1) from table1

Reseed the identity as below

DBCC CHECKIDENT (table1, RESEED, 220);

Now you can make use of the table along with identity as before

Advertisements
Categories: SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: