Adding an identity-based integer column

[Alan Jones]

I need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can't all NULLs. I have heard that this not possible if the table already exists.

I have downloaded the demo's of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.

Thanks for any help,

Alan

[Davy S]

Hi Alan,

If you do execute the following script, and replace the database and table names in the script, you should be ok.

/************************************************** ****************/

USE <database name>
go
EXEC sp_rename &#39;dbo.<table name>&#39;,&#39;<temp table name>&#39;,OBJECT
go
CREATE TABLE dbo.<table name>
(
PimKey int IDENTITY,
col1 varchar(20) NOT NULL,
col2 varchar(20) NULL,
col3 varchar(20) NULL
)
go
INSERT INTO dbo.<table name>(
-- PimKey,
col1,
col2,
col3
)
SELECT
-- 0,
col1,
col2,
col3

FROM dbo.<temp table name>
go


/************************************************** **************/

Also, I use DBArtisan by Embarcadero, and this product can accomplish the task you have requested.

Davy

ShipleyD@cwcom.net


------------
Alan Jones at 7/4/99 12:37:27 AM

I need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can&#39;t all NULLs. I have heard that this not possible if the table already exists.

I have downloaded the demo&#39;s of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.

Thanks for any help,

Alan

[Cindy Gross]

Don&#39;t forget grants, DRI, and indexes. Also, you&#39;ll want to check the row counts of the old and new tables to make sure they match. Make sure no users can access either the old or new table while you are making your changes.

------------
Davy S at 7/5/99 6:14:31 AM

Hi Alan,

If you do execute the following script, and replace the database and table names in the script, you should be ok.

/************************************************** ****************/

USE <database name>
go
EXEC sp_rename &#39;dbo.<table name>&#39;,&#39;<temp table name>&#39;,OBJECT
go
CREATE TABLE dbo.<table name>
(
PimKey int IDENTITY,
col1 varchar(20) NOT NULL,
col2 varchar(20) NULL,
col3 varchar(20) NULL
)
go
INSERT INTO dbo.<table name>(
-- PimKey,
col1,
col2,
col3
)
SELECT
-- 0,
col1,
col2,
col3

FROM dbo.<temp table name>
go


/************************************************** **************/

Also, I use DBArtisan by Embarcadero, and this product can accomplish the task you have requested.

Davy

ShipleyD@cwcom.net


------------
Alan Jones at 7/4/99 12:37:27 AM

I need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can&#39;t all NULLs. I have heard that this not possible if the table already exists.

I have downloaded the demo&#39;s of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.

Thanks for any help,

Alan