Adding field with identity to a table that already has data and populating existing records.

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)

Adding field with identity to a table that already has data and populating existing records.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-08 : 08:13:09

Martin writes "I'm trying to add a new "ID" field that will have the identity property set. The only catch is that this table already has hundreds of records and I need unique IDs to be added to those as well.

In Access, if you add a field with the autonumber datatype it automatically adds unique numbers to any existing records also.

I've looked through the ALTER TABLE options in the books online and can't seem to come up with a solution.

Any direction on this would be appreciated."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-08 : 08:20:01

I take it you didn't actually try it to see what happens...

Jay White
{0}

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-08 : 08:40:10

Like this
ALTER TABLE table1 ADD colname integer identity(1,1)

but now it will show up in EM as the last column if you want it
as if you added in EM in the first column here's a sample script that runs when you do it throught EM. You can generate one for you're self by making the change in EM and clicking on the script icon. Thanks tduggan.


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_Table1
	(
	ID int NOT NULL IDENTITY (1, 1),
	TestID int NOT NULL,
	TextCol ntext NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
IF EXISTS(SELECT * FROM dbo.Table1)
	 EXEC('INSERT INTO dbo.Tmp_Table1 (TestID, TextCol)
		SELECT TestID, TextCol FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
	PK_Table1 PRIMARY KEY CLUSTERED 
	(
	TestID
	) ON [PRIMARY]

GO
COMMIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources