Alter Column Question - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Alter Column Question

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-11-21 : 14:04:12

I have a table that contains an integer column with data. I would like to alter this column to an identity column.

How do I do this? This is a copy of a table that already exists.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 14:07:36

go to the EM.
choose design table.
change the column to identity.
click view change script and you'll see what you'd need to do in QA.

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-21 : 14:09:35

d'Oh

This is what Enterprise Mangler would do:

[CODE]
CREATE TABLE dbo.Tmp_myTable99
(
Col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1)
SELECT Col1 FROM dbo.myTable99 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
[/CODE]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources