Is there any way to alter a table column to remove the identity flag? Or create a table from another table structure without carrying over the identity flag?. A little backgroud:
I'm trying to create TSQL that appends multiple tables or queries (from separate DBs) that have the identical structure. This code will be generic and will not know the structure of the tables it is joining.
I created loop that uses the "select * into temp from table1" syntax to get create the table with the right table structure. All subsequent loops use the "insert * from table2 into temp" syntax.
The problem is that each table has an identity column (usually but not always called rowid) that overlaps with the other tables. When I run the program, I get the error:
"An explicit value for the identity column in table 'Temp' can only be specified when a column list is used and IDENTITY_INSERT is ON."
I tried using the Identity_insert TEMP ON but that didn't do anything.