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.
| Author |
Topic |
|
olibara
Yak Posting Veteran
94 Posts |
 Posted - 2012-06-13 : 02:55:35
|
HelloI'm trying to find the ALTER syntax to set Primary Key and Identity to an existing ColumnI'm looking around internet but this is the jungle to find pertinent or efficient SQL Syntax description  !Here is the DDL ogf my table I need to set MyKey as Primary Key and Identity ?[CODE]CREATE TABLE [dbo].[Test]( [MyKey] [int] NULL, [Name] [varchar](50) NULL)[/CODE] I have another little question : I see that if SSMS generate the CREATe Sript for this Table there is an added command "ON Primary"[CODE]CREATE TABLE [dbo].[Testu]( [MyKey] [int] NULL, [Name] [varchar](50) NULL) ON [PRIMARY][/CODE]what is the purpose of that ON PRIMARY ?It is also not clear ! Thanks for your help |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-13 : 03:45:48
|
| Can't change an existing column to identity.You can create a new column as identity, delete the old and rename the new.That will change the values - yoou can create a new table with an identity column and copy the data over retaining the values then delete the old table and rename the new.on primary is just the filegroup the table is on - I suspect you only have one so that will be the default anyway.Have a look at the cretae table statement in bol.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2012-06-13 : 04:37:48
|
| Thank you nigelrivettSo when you ask SSMS to set a column identity it does all that cooking in backround ? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-13 : 04:52:51
|
| Yep - you can get the script of what it does.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
SET identity and primary key on existing column