convert a column to identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

convert a column to identity

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-30 : 12:19:27

hi i have a table "iditem" with 2 columns "idproduct (int), price(money)"

but no column is identity

i have more than 200 records and im pretty sure i have no duplicate info in the column "idproduct" , but now i want to convert this column into identity

any idea how to do it

any hel will be appreciatte

thanks in advanced

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-30 : 12:24:21

I'm 99% sure that you can only add a new column as an identity.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-30 : 13:34:03

I'm 100% sure. You can't alter an existing column to add the identity property to it. In order for you to preserve you idproduct values you'd have to do something like this:

CREATE TABLE iditem2(idproduct int not null identity(1,1) primary key, price money not null)
SET IDENTITY_INSERT iditem2 ON
INSERT iditem2(idproduct,price) SELECT idproduct,price FROM iditem
SET IDENTITY_INSERT iditem2 OFF
EXEC sp_rename 'iditem','iditem_old','OBJECT'
EXEC sp_rename 'iditem2','iditem','OBJECT'

If something goes wrong, you still have the original iditem table.

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-30 : 14:55:27

Use rank function which suits your requirement and insert those values in new column which you want to use as identity.

GIGABYTE+

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-30 : 15:04:32

quote:
Originally posted by Gigabyte

Use rank function which suits your requirement and insert those values in new column which you want to use as identity.

GIGABYTE+

How does that help if there are gaps, negative numbers, or nothing applicable for the ORDER BY to properly rank? You still can't add an identity column and have it default to a RANK() function, it can only use the seed and increment.

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-30 : 16:45:58

Firstly, how would you have price of the product, when you do not have a product. I believe there is no possibility that we have nulls in id column.

Depends if we have negative numbers.

quote:
Originally posted by robvolk

quote:
Originally posted by Gigabyte

Use rank function which suits your requirement and insert those values in new column which you want to use as identity.

GIGABYTE+
How does that help if there are gaps, negative numbers, or nothing applicable for the ORDER BY to properly rank? You still can't add an identity column and have it default to a RANK() function, it can only use the seed and increment.

GIGABYTE+

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-30 : 17:46:00

quote:
Originally posted by Gigabyte

Firstly, how would you have price of the product, when you do not have a product. I believe there is no possibility that we have nulls in id column.

Depends if we have negative numbers.

Nulls are moot anyway, since if the ID was null, you couldn't insert it into a column defined with identity.

Nothing "depends" on negative numbers, since RANK() can't generate negatives and you'd have to do subtraction to get negatives.

I'm not sure what you're trying to describe. As I said earlier, and identity column can't accept RANK() as a default value. You can certainly use RANK() with IDENTITY_INSERT to insert them into a new table, but that's also moot as Sebastian wants to maintain the original IDs, just have them in an identity column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 17:57:11

As Rob pointed out you cant modify a column to be of identity

As a workaround, you can do like below, But its effectively creating a new table with identity column and then copying existing data to it

http://www.mssqltips.com/sqlservertip/1397/add-or-drop-identity-property-for-an-existing-sql-server-column/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-31 : 09:06:44

thanks everyone for your answers

thanks robvolk your solution works for me, awecome code, very helpfull , many many thanks.

thanks visakh16 , you are always helping

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 16:06:52

no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources