Just Learned - Ways to add primary key on existing table in SQL Server

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Ways to add primary key on existing table in SQL Server

Aug 23 2011 7:26AM by Paresh Prajapati

We can add primary key on existing table with following either query.

ALTER TABLE TableName 
ADD PRIMARY KEY (ColumnId)
GO

-- OR

ALTER TABLE TableName
ADD CONSTRAINT pk_ColumnId PRIMARY KEY(ColumnId)
GO

While creating primary key on existing table which have so many number of rows, i have reviewed sql text from runing processes and i found following command.

INSERT [DBO].[TableName] SELECT * FROM [DBO].[TableName]

It seems to be recreating a new table and drop existing then after.

4 Comments

I knew one (the creation of the primary keys), but didn't know the other. Thank-you for sharing.

However, it would be interesting to see an actual field use of using a INSERT INTO...SELECT... using the same table as destination and source.

commented on Aug 23 2011 9:03AM

Nakul Vachhrajani

4 · 33% · 10587

Thanks for sharing the internal process. INSERT [DBO].[TableName] SELECT * FROM [DBO].[TableName]

commented on Aug 23 2011 11:26PM

Sathieshkumar

703 · 0% · 46

Yes Nakul,

I could not figured out it why source and destination table are same for internal process. I got exactly same statement appeared as above just without any fields or extra information.

commented on Aug 24 2011 12:40AM

Paresh Prajapati

6 · 22% · 7054

Paresh i think it because it would reorder the data of table if we are putting clustered index on table ... may be one of this reason but not 100% sure.

commented on Aug 26 2011 4:29AM

Raj Acharya

1940 · 0% · 8

Your Comment

Sign Up or Login to post a comment.

"Ways to add primary key on existing table in SQL Server" rated 5 out of 5 by 9 readers +Paresh Prajapati