Changing a column to an Identity column. Please Help...

I have a situation where we converted over a large database. The database
had one table that used autonumber(Access) that we need to preserve the IDs
upon conversion for a table that refereneces the field. So during
conversion I turned off the identity and it brought over the ID column with
the correct values...but now I find that I can't turn the Identity back on
for that column!!!!!
I am in big trouble here...is there some way to take an existing int, no
null column into an identity column'
Please help.
Thanks,
Ron"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the
> IDs upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column
> with the correct values...but now I find that I can't turn the Identity
> back on for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
First of all, you didn't need to turn it off, you could have used SET
IDENTITY_INSERT.
How did you turn it off? With Enterprise Manager?
You should be able to modify the column to Identity with EM.|||Hi
Did you use "SET IDENTITY_INSERT ON" then if you are still using the same
session then you can use SET IDENTITY_INSERT OFF, other/new sessions will no
t
be affected.
If you removed the IDENTITY property, then you will have to create a new
column with the identity property and move the data into it using the above
commands to allow insertion.
John
"RSH" wrote:

> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
>
>|||I tried this but I still get the ERROR:
Msg 8102, Level 16, State 1, Line 2
Cannot update identity column 'ID'.
SET IDENTITY_INSERT [TESTACH].[dbo].[DirectDeposit] ON
GO
UPDATE [TESTACH].[dbo].[DirectDeposit] SET ID = 73986 WHERE DirectDepositID
= 73986
GO
What am I doing wrong'
THANKS!
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uHwK7ZZBGHA.2840@.TK2MSFTNGP12.phx.gbl...
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> First of all, you didn't need to turn it off, you could have used SET
> IDENTITY_INSERT.
> How did you turn it off? With Enterprise Manager?
> You should be able to modify the column to Identity with EM.
>sql

Changing a column to an Identity column. Please Help...

RSH wrote:
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
You can't change this property for an existing column and the IDENTITY
value can only be set on INSERT. What this means is that you'll have to
create a new table and INSERT the existing data into it using the SET
IDENTITY_INSERT option. You can then drop the old table and rename the
new one.
If you modify the table in Enterprise Manager (SQL Server 2000) or
Management Studio (2005) then it will make the change for you or will
generate a script to do it.
David Portas
SQL Server MVP
--Okay next question...is there a simple query that will allow me to do
something like...
INSERT * FROM ACHMaster INTO ACH
?
I need someway to perform the insert from one table to the other.
Thanks for all of your help!!!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
> RSH wrote:
> You can't change this property for an existing column and the IDENTITY
> value can only be set on INSERT. What this means is that you'll have to
> create a new table and INSERT the existing data into it using the SET
> IDENTITY_INSERT option. You can then drop the old table and rename the
> new one.
> If you modify the table in Enterprise Manager (SQL Server 2000) or
> Management Studio (2005) then it will make the change for you or will
> generate a script to do it.
> --
> David Portas
> SQL Server MVP
> --
>|||INSERT ACH SELECT * FROM ACHMaster
Note: SELECT * shouldn't be used for production queries. For a one-time
fix, however, it should work, provided the tables were created with the
columns in the same order. It is always best to spell out the column list,
both for the INSERT and the SELECT.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
> Okay next question...is there a simple query that will allow me to do
> something like...
> INSERT * FROM ACHMaster INTO ACH
> ?
> I need someway to perform the insert from one table to the other.
> Thanks for all of your help!!!!
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
>|||THANKS ALOT!!!!!!
--Ron
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uybyzPbBGHA.3292@.TK2MSFTNGP09.phx.gbl...
> INSERT ACH SELECT * FROM ACHMaster
> Note: SELECT * shouldn't be used for production queries. For a one-time
> fix, however, it should work, provided the tables were created with the
> columns in the same order. It is always best to spell out the column
> list, both for the INSERT and the SELECT.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
>