How to add IDENTITY constraint

Dear All,

I wanted to add a IDENTITY constraint in a column using the ALTER command, How do I go about doing that?

Please help.

Code Snippet

create table #what (something varchar(10))

alter table #what
add anIdentity int identity

insert into #what
select 'This' union all
select 'is' union all
select 'just' union all
select 'another' union all
select 'test'
select * from #what

/*
something anIdentity
- --
This 1
is 2
just 3
another 4
*/

|||

Sorry for incomplete or unclear question.

What I mean is Altering the existing column and adding Identity to that column

|||

I think to do that you will need to do the work of creating a new table and move the data into the new table. Care needs to be take to pay attention to the seeding of the new identity column and all constraint work that needs to be done.

Will someone please check me on this?

|||

Unfortunately, i don't think you are able to alter an existing column to be an identity. Your best workaround is to create a new table with the correct schema, dump the data into it and then rename it.

However, I appreciate this can be a messy and impractical approach. Your only other option is to just add an extra identity column.

HTH

|||The following should work:

Code Snippet

CREATE TABLE TestTable
(
ToBeIdentity INT,
TestCol VARCHAR(20)
)

INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(1, 'Col1')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(2, 'Col2')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(3, 'Col3')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(4, 'Col4')

SELECT
*
INTO
#TempTransfer
FROM
TestTable

ALTER TABLE TestTable DROP COLUMN ToBeIdentity

DELETE FROM TestTable

ALTER TABLE TestTable ADD ToBeIdentity INT IDENTITY(1, 1)

SET IDENTITY_INSERT TestTable ON
INSERT INTO TestTable
(ToBeIdentity,
TestCol)
SELECT
*
FROM
#TempTransfer
SET IDENTITY_INSERT TestTable OFF

INSERT INTO TestTable(TestCol) VALUES('Col5')

SELECT
*
FROM
TestTable

DROP TABLE #TempTransfer

Unfortunately, you have to specify a column list when inserting the data into the table again. Other than that, you should be able to use it for most tables without modification.
|||

It is not necessary to create a new table and transfer the data -with a very large table that could be quite a performance hit.

As this example demonstrates, you can add an IDENTITY column to an existing table. (NOTE: This does not guarantee any order to the data.)

Code Snippet

CREATE TABLE #MyTable
( RowID int,
MyValue varchar(20)
)

INSERT INTO #MyTable VALUES ( 25, 'Value 1' )
INSERT INTO #MyTable VALUES ( 2, 'Value 2' )
INSERT INTO #MyTable VALUES ( 61, 'Value 3' )
INSERT INTO #MyTable VALUES ( 33, 'Value 4' )

-- Keep Existing Column and Data
ALTER TABLE #MyTable
ADD NewRowID int IDENTITY(1, 1)

SELECT *
FROM #MyTable

RowID MyValue NewRowID
-- -- --
25 Value 1 1
2 Value 2 2
61 Value 3 3
33 Value 4 4

-- If you want to keep the Column Name -but not data
ALTER TABLE #MyTable
DROP COLUMN RowID, NewRowID

ALTER TABLE #MyTable
ADD RowID int IDENTITY(1, 1)

SELECT *
FROM #MyTable

DROP TABLE #MyTable

MyValue RowID
-- --
Value 1 1
Value 2 2
Value 3 3
Value 4 4

If you want to maintain or coerce order, creating a new table and transferring the data would allow you to use ORDER BY on the insert statement.

|||

Arnie Rowland wrote:

It is not necessary to create a new table and transfer the data -with a very large table that could be quite a performance hit.

As this example demonstrates, you can add an IDENTITY column to an existing table.

Code Snippet

CREATE TABLE #MyTable
( RowID int,
MyValue varchar(20)
)

...

Your foreign keys on the original column become messed up unless you preserve the original values.