adding an identity column retrospectively

I've inherited a database that has a series of tables that have primary keys
that SHOULD also be identity columns, but don't.
e.g.
CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[fieldA] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
They've now got loads of data in them.
These are updated by a set of stored procedures that first identify the
max(id) value and then insert the id as max(id) + 1.
As you can imagine, there's lots of errors where, having identified the
max(id) value, another instance has nabbed the incremented value, causing
the database to generate an error where it's attempting to create a
duplicate primary key.
What I want to do is to update the database tables so that they have an
identity (1,1) set on them.
Bizarrely, I can't get the syntax right...
Secondly (and more importantly)
Could adding this identity column (and of course updating all the related
stored procedures) create any problems? Could it damage any of the data
already in the database?
Thanks in advance
Griffhttp://www.windowsitpro.com/Article...2080/22080.html
"Griff" <howling@.the.moon> wrote in message
news:eLeJ8jtPGHA.2992@.tk2msftngp13.phx.gbl...
> I've inherited a database that has a series of tables that have primary
> keys that SHOULD also be identity columns, but don't.
> e.g.
> CREATE TABLE [dbo].[test] (
> [id] [int] NOT NULL ,
> [fieldA] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[test] ADD
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> GO
> They've now got loads of data in them.
> These are updated by a set of stored procedures that first identify the
> max(id) value and then insert the id as max(id) + 1.
> As you can imagine, there's lots of errors where, having identified the
> max(id) value, another instance has nabbed the incremented value, causing
> the database to generate an error where it's attempting to create a
> duplicate primary key.
> What I want to do is to update the database tables so that they have an
> identity (1,1) set on them.
> Bizarrely, I can't get the syntax right...
> Secondly (and more importantly)
> Could adding this identity column (and of course updating all the related
> stored procedures) create any problems? Could it damage any of the data
> already in the database?
> Thanks in advance
> Griff
>