Create identity field on existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create identity field on existing table

Author

Topic

astralis
Yak Posting Veteran

62 Posts

Posted - 2014-05-17 : 17:27:25

What is the best practice?

Or should I just start a new table?

I really don't need the current table because all the data is test data, but would rather not start a new one.

Also, if I do start a new table, can I simply exchange table names later (so I don't need to change the application's code)?

For example, current table name:

TableA

Rename to:

TableA_Old

Create new table:

TableA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-18 : 21:45:59

quote:
I really don't need the current table because all the data is test data

i would script the create script of the table, drop existing table, re-create with the identity field

KH
[spoiler]Time is always against us[/spoiler]

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-19 : 09:45:25

You can simply add the identity column:

ALTER TABLE TableA
ADD ident int IDENTITY(1, 1)

adsingh82
Starting Member

20 Posts

Posted - 2014-05-20 : 01:43:15

Yes you can go for simple alter if your production table is small with less than a million record. If it is bigger then you have to create the table with identity column and have to go for insert and rename

Regards,
Alwyn.M

stevieb
Starting Member

13 Posts

Posted - 2014-05-20 : 03:46:43

I use the following to add a primary key to a newly created table.

Use db_yourdatabse
ALTER TABLE dbo.yourtable
ADD ID INT IDENTITY
ALTER TABLE dbo.yourtable
ADD Constraint PK_dbo.yourtable
PRIMARY KEY(ID)
GO

It may not be the best way as I am no SQL expert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources