SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Alter table column for primery key and identity
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 08/10/2012 :  01:58:12  Show Profile  Reply with Quote
I have table created and want to alter that table using script.
I want to apply primary key and identity(1,1) using script.

I can apply primary key but applying identity gives error. Is anything missing?

ALTER TABLE MyTable ADD PRIMARY KEY (Id)

How can I add identity as well with primary key?

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 08/10/2012 :  03:34:46  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
1- You cann't ALTER COLUMN for identity
2- Primary Key can added with ALTER TABLE
3- If need to add identity to existing column, copy your data to temp table (or other user table), drop orignal, recreate with identity changes and bring back you data.

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 08/10/2012 :  04:20:29  Show Profile  Reply with Quote
yes.. got it thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/10/2012 :  10:55:42  Show Profile  Reply with Quote
quote:
Originally posted by keyursoni85

yes.. got it thanks


see

http://www.mssqltips.com/sqlservertip/1397/add-or-drop-identity-property-for-an-existing-sql-server-column/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000