How to alter a column - 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)

How to alter a column

Author

Topic

cbecerra
Starting Member

38 Posts

Posted - 2001-08-24 : 13:54:49

I'm trying to make an existing column into an identity column.

ALTER TABLE newtable ALTER COLUMN rtid int(4) IDENTITY(1 , 1 ) not null

However I keep getting a syntax error, does anyone have a clue to what I'm doing wrong.

Thanks

Corobori
Posting Yak Master

105 Posts

Posted - 2004-03-08 : 14:59:13

I am also looking to do this but I am getting the same error. Anybody who solved it since 2001 ?

ALTER TABLE tblTiersGérant
ALTER COLUMN TiersGérant int identity not null

jean-luc
www.corobori.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-08 : 20:34:17

You have to specify a seed and increment:

ALTER TABLE tblTiersGérant ALTER COLUMN TiersGérant int identity(1,1) not null

DxSolo
Starting Member

1 Post

Posted - 2004-03-24 : 11:46:20

ALTER TABLE [dbo].[Photos] DROP CONSTRAINT [PK_Photos_1__52]

GO

alter table photos drop column photoid

alter table photos add photoid bigint identity(1,1)

ALTER TABLE [dbo].[Photos] ADD CONSTRAINT [PK_Photos_1__52] PRIMARY KEY NONCLUSTERED
(
[PhotoID]
) ON [PRIMARY]
GO

this is what i did to add an identity field to an existing field that also had a primary key and it updated the data for me hope this helps

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 12:48:41

Elegant...no...

Here's what EM Generates..



BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_myTable99
	(
	Col1 int NOT NULL IDENTITY (1, 1),
	Col2 char(1) NOT NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
	 EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, Col2)
		SELECT Col1, Col2 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
CREATE UNIQUE CLUSTERED INDEX myTable99_IX1 ON dbo.myTable99
	(
	Col1
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX myTable99_IX2 ON dbo.myTable99
	(
	Col2
	) ON [PRIMARY]
GO
COMMIT

Brett

8-)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-24 : 20:05:47

Yeah, I've found that EM has a penchant for creating a copy of the table, moving all the rows, then renaming, etc. when you script out table changes. As you say, Brett, not elegant, but it works.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources