How to alter column to identity(1,1)

I tried

Alter tabletable1 alter columncolumn1 Identity(1,1);

Alter tabletable1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1

they all can not work,

any idea about this? thanks

June 18th, 2007 11:21am

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

Free Windows Admin Tool Kit Click here and download it now
June 18th, 2007 11:49am

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 cant 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'

June 18th, 2007 12:02pm

thank you

butI could not change the existing number order and add identity

any idea about this?

Free Windows Admin Tool Kit Click here and download it now
June 19th, 2007 2:19am

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.
June 19th, 2007 4:48am

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 namesin object explorer window

right click oncolumn 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 nextvalue (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 thisresponse helps and isnt too late... Jane.

Free Windows Admin Tool Kit Click here and download it now
October 30th, 2007 8:05pm

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.

June 27th, 2008 5:35pm

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 implementationwhat you can do manually.

Free Windows Admin Tool Kit Click here and download it now
August 4th, 2008 9:11am

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.
January 26th, 2010 11:42am

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
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2010 10:36am

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.

November 18th, 2010 11:05am

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.
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2012 6:03pm

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

(Note that the 4 steps above are prerequisites for this technique. Those are really simple steps, so I figured they didn't need much more explanation; however, the procedure that follows it to perform the switcheroo between the table that doesn't have an identity and the table that does can be a bit touchier)

here comes the magic:

--1.Disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
--2."Switch TO" everything from the existing table to the new table
ALTER TABLE [tablename] SWITCH TO [tablename2]
--3.Drop the table that had no identity drop table [tablename]
--4. Rename the table with new identity column to old table name sp_rename 'tablename2','tablename'

--5. Rename pk to match old pk name sp_rename 'pk_tablename2','pk_tablename'
--6. Re-enable all constraints exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


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







  • Edited by K. Alan Bates Monday, April 20, 2015 5:24 PM NOTE1: If you have any foreign keys, you'll have to "fk_name2" them and then run them through sp_rename after the SWITCH TO| Note 2: Added step comments directly to code for legibility
June 13th, 2012 7:57pm

Hi All,

Another post for the same. Please try it.

http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

Free Windows Admin Tool Kit Click here and download it now
June 3rd, 2013 4:16am

Starting with SQL Server 2012 SEQUENCE object can be used instead of IDENTITY:

CREATE SEQUENCE Sales.SalesOrderHeaderIdent
    START WITH 1
    INCREMENT BY 1 ;
GO

SELECT NEXT VALUE FOR Sales.SalesOrderHeaderIdent;
GO 50

June 3rd, 2013 5:11am

Hi Toth,

It is great. Here is the complete info about CREATE SEQUENCE in 2012

http://msdn.microsoft.com/en-us/library/ff878058.aspx

Free Windows Admin Tool Kit Click here and download it now
June 5th, 2013 12:46am

Hi Toth,

I got one doubt: 

If source is other than SQL Server & need to generate this sequence no then How can we do this in ETL? Please can you suggest if any work around?

June 5th, 2013 1:45am

>If source is other than SQL Server & need to generate this sequence

You import the source first into a SQL Server staging table:

CREATE SEQUENCE NotSSSource
    START WITH 1
    INCREMENT BY 1 ;
GO
CREATE TABLE StageNotSSSource (
ID INT DEFAULT (NEXT VALUE FOR NotSSSource),
ProductID int,
ListPrice smallmoney)
GO
INSERT StageNotSSSource (ProductID, ListPrice)
SELECT ProductID, ListPrice 
FROM AdventureWorks2012.Production.Product
WHERE Color is NOT NULL;
GO
SELECT * FROM StageNotSSSource ORDER BY ID;
GO
/*
ID	ProductID	ListPrice
....
28	721	1489.3326
29	722	350.8437
30	723	350.8437
31	724	350.8437
32	725	350.8437
33	726	350.8437  .... */

Free Windows Admin Tool Kit Click here and download it now
June 14th, 2013 2:31pm

ALTER TABLE table ADD column int NOT NULL IDENTITY (1, 1)
July 30th, 2013 3:01pm

IDENTITY is a table property, not a column. It is also not relational and an SQL programmer would never use it. How can the count of physical insertion attempts to one table on one installation of one vendor's SQL product be part of a valid data model? 
Free Windows Admin Tool Kit Click here and download it now
July 30th, 2013 3:29pm

Basically there are four logical steps.

1. Create a new Identity column. Turn on Insert Identity for this new column.

2. Insert the data from the source column (the column you wished to convert to Identity) to this new column.

3. Turn off the Insert Identity for the new column.

4. Drop your source column & rename the new column to the name of the source column.

There may be some more complexities like working across multiple servers etc.

Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

April 19th, 2014 6:46am

An easier way is to Go to Tools>Designers>Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation."  You can always turn the function back on after you're happy with your table structures.

Free Windows Admin Tool Kit Click here and download it now
August 15th, 2014 10:57pm

It looks like very nice solution for alter column as identity instead of creating a new table a d renaming.

What's your thought?

July 10th, 2015 2:08am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics