ADD IDENTITY PROPERTY WHEN THERE IS ALREADY DATA

Dear all,
Keep in mind the structure of the following table I would need alter ID
field and add an IDENTITY property but when data are already loaded.
The source table begin from 4200 as value in the first row and if before of
that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from 1
.
And so that it's a disaster.
Let me know how would I work out this issue.
CREATE TABLE [dbo].[VIA_DebugInfo] (
[Id] [int] NOT NULL ,
[Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
This one doesn't work, I haven't idea!
SET IDENTITY_INSERT via_debuginfo OFFCheck out the seed in the BOL
this is what I have done
CREATE TABLE [Claim] (
[ClaimID] [int] IDENTITY (15621, 1) NOT NULL ,
...
...
...
I would assume that you would ALTER the table add the new column, setting
its seed to the number you want
RObert
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:4DEDB4D8-BDA2-4985-BE46-287F1FADB537@.microsoft.com...
> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before
of
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF|||Enric donotspam wrote:
> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before o
f
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF
You can't make an existing column an IDENTITY.
You could add a new IDENTITY column. Using the IDENTITY seed argument
you can start the sequence at 4200 but you can't control which row gets
which value. Failing that you have to create a new table. Wisest option
is to avoid ascribing any business meaning to an IDENTITY column. If
you stick to that principle you won't have this problem.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||tHANKS TO BOTH FOR YOUR HELP
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Enric" wrote:

> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before o
f
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF