Columns IDENTITY property

Hi all.
I have inherited some table full of data. It has a primary key [int]
clustered on one column. But this column was not created with IDENTITY. Now
I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
loosing the data but can't figure out the syntax of proper script :( I know
that EM allows you to do just that but I need a script).
Could anyone help me with that, please? Is it possible at all? Books Online
doc is not clear about this situation.
Thanks!!Here is the script that EM generates to add Identity to a column:
As you can see, you must create a new table,
copy the data from the old table to the new,
drop the old table and rename the new one.
This is a very simple example.
For a table with constraints, triggers, foreign keys etc., the script gets
longer.
On another table I have, the same operation generates about 300 lines of
code.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_numbers
(
number int NOT NULL,
test int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_numbers ON
GO
IF EXISTS(SELECT * FROM dbo.numbers)
EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
SELECT number, test FROM dbo.numbers TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_numbers OFF
GO
DROP TABLE dbo.numbers
GO
EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
GO
COMMIT
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY.
Now
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I
know
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books
Online
> doc is not clear about this situation.
> Thanks!!
>
>|||You can not do it using ALTER TABLE. Set the identity property in EM and
click "Save change script" button (third from left to rigth).
AMB
"Kikoz" wrote:

> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY. No
w
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I kno
w
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books Onlin
e
> doc is not clear about this situation.
> Thanks!!
>
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>