Add identity with ALTER TABLE SQL statement

[PoppapumpJ]

I recently converted a large Access database to SQL server.

My web hosting company performed the migration using Access' Upsizing Wizard and the auto-increment setting was not applied to any of my primary keys.

The hosting company will not tell me the IP address of the SQL server database. All they give me is a connection string and the server name is local to their network so I'm unable to access it using any kind of administration tools.

I can run SQL statements against the database.

Can anyone point me to an example of an ALTER TABLE statement that can turn an existing column into an Identity column?

I've found pleny of CREATE TABLE examples, but no ALTER TABLE examples.

Thanks

 

[mrdenny]

There is no ALTER TABLE command to change an integer column to an IDENTITY column. You have to create a new table with an identity column, load the data into the table with the IDENTITY_INSERT flag ON, then delete the old table, and rename the new table to the old table's name.

I created a table called test, with two columns. One an integer, and one nchar(10). This would be the code to make the change.

BEGIN TRAN
CREATE TABLE dbo.Tmp_test
	(
	test int NOT NULL IDENTITY (1, 1),
	sdf nchar(10) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_test ON
GO
IF EXISTS(SELECT * FROM dbo.test)
	 EXEC('INSERT INTO dbo.Tmp_test (test, sdf)
		SELECT test, sdf FROM dbo.test WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_test OFF
GO
DROP TABLE dbo.test
GO
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT' 
GO
COMMIT
GO

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog