Alter Column to IDENTITY

I used Select Into... to duplicate a table, but lost the IDENTITY and PK
constraints in the process. How do I get those back? Thanks.
Here are the details:
Original Table:
TABLE A (
[ID] INT IDENTITY PRIMARY KEY
, ColA smallint NOT NULL
, ColB varchar(20) NOT NULL
, ColC int NULL
, ColD numeric(38, 6) NULL
)
After SELECT * INTO TABLE_B FROM TABLE_A:
TABLE B (
[ID] INT NOT NULL
, ColA smallint NOT NULL
, ColB varchar(20) NOT NULL
, ColC int NULL
, ColD numeric(38, 6) NULL
)
I tried something like this, but it didn't work:
ALTER TABLE TABLE_B
ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY
You can't do this; T-SQL does not allow you to add or drop the IDENTITY
property. What you need to do can be better illustrated by performing the
action in Enterprise Mangler and viewing the code that it produces. It's
rather ugly, basically it creates a work table, moves all the data over,
drops the original and renames the new. All with a huge xlock, of course.
I shouldn't have to tell you what this means on a large-ish table.|||Short answer - you can't. But you can add a *new* identity column to the
table then drop the old column. Easiest way might be to make the change via
EM - design table and save the change script.
HTH
Jerry
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:6E0241AC-792C-4D1A-92E4-9CFCF99413AF@.microsoft.com...
>I used Select Into... to duplicate a table, but lost the IDENTITY and PK
> constraints in the process. How do I get those back? Thanks.
> Here are the details:
> Original Table:
> TABLE A (
> [ID] INT IDENTITY PRIMARY KEY
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> After SELECT * INTO TABLE_B FROM TABLE_A:
> TABLE B (
> [ID] INT NOT NULL
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY|||Drop the column and add it again as an Identity column
http://sqlservercode.blogspot.com/
"Boddhicitta" wrote:

> I used Select Into... to duplicate a table, but lost the IDENTITY and PK
> constraints in the process. How do I get those back? Thanks.
> Here are the details:
> Original Table:
> TABLE A (
> [ID] INT IDENTITY PRIMARY KEY
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> After SELECT * INTO TABLE_B FROM TABLE_A:
> TABLE B (
> [ID] INT NOT NULL
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY|||Thanks everyone. These are all kind of yucky for different reasons. Is
there a better way to copy a table and retain the constraints? DTS?
"Boddhicitta" wrote:

> I used Select Into... to duplicate a table, but lost the IDENTITY and PK
> constraints in the process. How do I get those back? Thanks.
> Here are the details:
> Original Table:
> TABLE A (
> [ID] INT IDENTITY PRIMARY KEY
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> After SELECT * INTO TABLE_B FROM TABLE_A:
> TABLE B (
> [ID] INT NOT NULL
> , ColA smallint NOT NULL
> , ColB varchar(20) NOT NULL
> , ColC int NULL
> , ColD numeric(38, 6) NULL
> )
> I tried something like this, but it didn't work:
> ALTER TABLE TABLE_B
> ALTER COLUMN [ID] INT IDENTITY PRIMARY KEY|||Sure, create the table with the constraints in place, then insert the data
(specifying all columns except the identity column).
A
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:40361790-544E-4404-91B0-8F88D1F97B97@.microsoft.com...
> Thanks everyone. These are all kind of yucky for different reasons. Is
> there a better way to copy a table and retain the constraints? DTS?|||Try this:
CREATE TABLE B (
[ID] INT IDENTITY PRIMARY KEY
, ColA smallint NOT NULL
, ColB varchar(20) NOT NULL
, ColC int NULL
, ColD numeric(38, 6) NULL
)
GO
SET IDENTITY_INSERT B ON
GO
INSERT INTO B ([ID], ColA, ColB, ColC, ColD)
SELECT [ID], ColA, ColB, ColC, ColD FROM A
SET IDENTITY_INSERT B OFF
GO
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:40361790-544E-4404-91B0-8F88D1F97B97@.microsoft.com...
> Thanks everyone. These are all kind of yucky for different reasons. Is
> there a better way to copy a table and retain the constraints? DTS?
> "Boddhicitta" wrote:
>|||> Enterprise Mangler
LOL!! Thanks, I really needed that! HA HA HA HA!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Oops, typo. :-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23j9uk1O1FHA.3376@.TK2MSFTNGP14.phx.gbl...
> LOL!! Thanks, I really needed that! HA HA HA HA!|||While I grateful for the DDL, I hope that you are not you actaully
using IDENTITY as a key? FUNDAMENTA! WRONG ! Look for a relational
key.
Surely you had enough basic to know that code like "IDENTITY PRIMARY
KEY " means thart you have ABSOLUTELY NO CONCEPT OF RbMS.