How to alter column to identity(1,1)

Question

I tried

 

Alter table table1 alter column column1 Identity(1,1);

Alter table table1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1

 

they all can not work,

any idea about this? thanks

 

Answer 1

 

Code Snippet

--create test table

create table table1 (col1 int, col2 varchar(30))

insert into table1 values (100, 'olddata')

 

 --add identity column

alter table table1 add col3 int identity(1,1)

GO

 

--rename or remove old column

exec sp_rename 'table1.col1', 'oldcol1', 'column'

OR

alter table table1 drop column col1

--rename new column to old column name

exec sp_rename 'table1.col3', 'col1', 'column'

GO

 

--add new test record and review table

insert into table1 values ( 'newdata')

select * from table1

 

Answer 2

 

 

You can't alter the existing columns for identity.

 

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

 

But take spl care when these columns have any constraints / relations.

 

 

Code Snippet

/*

            For already craeted table Names

            Drop table Names

            Create table Names

            (

                        ID int,

                        Name varchar(50)

            )

 

            Insert Into Names Values(1,'SQL Server')

            Insert Into Names Values(2,'ASP.NET')

            Insert Into Names Values(4,'C#')

*/

 

 

Code Snippet

--In this Approach you can retain the existing data values on the newly created identity column

CREATE TABLE dbo.Tmp_Names

            (

            Id int NOT NULL IDENTITY (1, 1),

            Name varchar(50) NULL

            )  ON [PRIMARY]

 

go

SET IDENTITY_INSERT dbo.Tmp_Names ON

 

go

IF EXISTS(SELECT * FROM dbo.Names)

            INSERT INTO dbo.Tmp_Names (Id, Name)

                        SELECT Id, Name FROM dbo.Names TABLOCKX

 

go

SET IDENTITY_INSERT dbo.Tmp_Names OFF

 

go

DROP TABLE dbo.Names

 

go

 

Exec sp_rename 'Tmp_Names', 'Names'

 

 

Code Snippet

--In this approach you can’t retain the existing data values on the newly created identity column;

--The identity column will hold the sequence of number

 

Alter Table Names Add Id_new Int Identity(1,1)

Go

 

Alter Table Names Drop Column ID

Go

 

Exec sp_rename 'Names.Id_new', 'ID','Column'

 

 

 

Answer 3

thank you

but I could not change the existing number order and add identity

any idea about this?

 

Answer 4

Nope.  There is no way to alter a column to have the identity property.  You will have to create a new table and insert into it if you want initial control over the values in the column.

Answer 5

If you have ms sql srvr mgmt studio you can do it.  You just have to do it manually, not in script or command form.

 

connect to your db

show the table/column names in object explorer window

right click on column name

select modify

in column properties tab

   alter "identity specification"/"is identity" to YES

   set increment/seed properties as desired

 

Note:  if you leave it at default 1,1 it will automatically insert the next row with the appropriate next value (not 1)

 

     Also, I had dup vals in the column but my primary key is a compound key with that and another field and I had no problem accessing the old data - just needed to keep the key with both fields.

 

I hope this response helps and isnt too late... Jane. 

 

 

Answer 6

In the management studio, drill down to the table and then to the column you want to change to identity. Right click on the column and select Modify option. At the bottom of the screen, you will find column properties. Scroll down tha list and you will see "Identity Specification". Expand that and change (Is Identity) to Yes and give in the Seed (just make sure it is after the latest value that you already have in the table just to avoid conflicts later on) and incremental values and save. That should do the trick.

 

I had rows existing in my table and managed to keep them with the existing values. New rows loaded did have the id value automatically generated. Did not generate from the seed that I gave, but I am not sure if it was because of rows that had previously existed in the table that I had deleted earlier.

 

Hope this helps.

Answer 7

You can do it manually through 'Management Studio' but there appears to be a bug which stops you from doing it through SQL. There is even Microsoft sample code:-

 

CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)

which doesn't work on SQL Server 2005 queries.

What you have to do is create a table with the 'identity' column, but with the 'Identity_Insert' property set so that you can put values into it. Unfortunately you must set the 'Identity_Insert' on the server itself (not a linked server) so that everything gets done in the same session. And you have to unset it before you can set it again, even for another table. Very shabby. And all because you can't do in this SQL implementation what you can do manually.

 

 

 

Answer 8

If by manually you mean using the Management Studio user interface then it's really automatically. Management studio will run a script that will create a new table, copy the data across for you and drop the original table. Unless you tick Prevent saving changes that require table re-creation in the Options, in which case it will refuse to make this change.

Answer 9

you do not need to drop the table or column. You have been given bogus misinformation, which is annoying appearing in SQL-Server Help

I use  DBCC Checkident with reseed option to reset identity columns

this example is in books online

USE AdventureWorks; GO DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30); GO

with this you can alter the column for identity.

---

fa

Answer 10

you do not need to drop the table or column. You have been given bogus misinformation, which is annoying appearing in SQL-Server Help

I use  DBCC Checkident with reseed option to reset identity columns

this example is in books online

 

USE AdventureWorks; GO DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30); GO

with this you can alter the column for identity.

 

---

fa

@Farid... thats why they call half knowledge is dangerous thing.

DBCC CHECKIDENT only applies to Identity columns, if a table have it. {MS BOL: Checks the current identity value for the specified table in SQL Server 2008 R2 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.}

Here the table does not contain any IDENTITY column and @Andre_s wants to change the column property to IDENTITY. Which is not possible by the ALTER statement.

You would need to re-create table load the data again and drop the old table.

OR... SSMS do this graphically, but internally it also recreate the table, load the data & drops the table.

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 11

Hi, I followed you code snippet #2, trying to retain existing data values for the new identity column, here is the error I got: "cannot insert explicit value for identity column in table "tableName" when Identity_insert is set to OFF. Thanks.

---

Sarah Wang

Answer 12

The easiest way I have found to add an identity to an existing column (assuming you don't have direct access through SSMS) is to

1. Script out the table definition to a query
2. Rename the table in the script and add identity(1,1) to the column you need the identity on
3. Rename the primary key (I usually use the same name and put a 2 behind it)
4. Run the create table script

here comes the magic:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
ALTER TABLE [tablename] SWITCH TO [tablename2]
drop table [tablename]
sp_rename 'tablename2','tablename'
sp_rename 'pk_tablename2','pk_tablename'
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

1.Disable all constraints
2."Switch TO" everything from the existing table to the new table
3.Drop the table that had no identity
4. Rename the table with new identity column to old table name
5. Rename pk to match old pk name
6. Re-enable constraints


You'd be surprised how much more difficult it is to explain it than it is to actually do it.