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...
>