Preserving Identity column : Question and Answer

[petercon]

I am trying to back up and restore a table with an identity column
without losing the value of that column.

I have copied the data into a back up table using:
'insert into tblmy_back
select * from tblmy'
tblmy_back does not have an identity column, this preserves the value.

After dropping and redefining tblmy without the identity column I load it with 'insert into tblmy select * from tblmy_back'
I cannot alter the column back to an identity column.
How can I get around this problem?

Answer: from asp.net forum at msn

set identity_insert tblcalculation on

before reloading with :

insert into tblmy_back
select * from tblmy



HPC

[bruce]

This reference may be able to help you

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp

See sample sql statement D
D. Load data into a table with an identity column

[b]quote:Originally posted by petercon

I am trying to back up and restore a table with an identity column
without losing the value of that column.

I have copied the data into a back up table using:
'insert into tblmy_back
select * from tblmy'
tblmy_back does not have an identity column, this preserves the value.

After dropping and redefining tblmy without the identity column I load it with 'insert into tblmy select * from tblmy_back'
I cannot alter the column back to an identity column.
How can I get around this problem?

Answer: from asp.net forum at msn

set identity_insert tblcalculation on

before reloading with :

insert into tblmy_back
select * from tblmy



HPC
</blockquote id="quote"></font id="quote">