April 22, 2008 at 10:44 am
I've got a temporary table (#mytable) that mirrors the structure of a permanent table (mytable). Both have identity columns as I create the #mytable based on a 'select top 1 * into #mytable from mytable' and then I truncate #mytable. After I create the records in the temporary table, I want to move those over to the permanent table with a specific order (hence messing around with a temporary table). Since the table has quite a few columns and it does change once in awhile, I don't want to explicitly state each field when I do the final insert 'insert into mytable select * from #mytable order by xyz'. Is it possible to do something like that without running into identity errors? In other words, is there a way to do an 'insert into ... select *...' but exclude the identity column?
I suppose I could turn off the identity_insert and start the identity column of the temporary table with the max(id) of the permanent table, but if there are other inserts to the permanent table while this procedure is running, I still could run into identity error.
Much appreciated for any insights.
Thanks,
David
April 22, 2008 at 12:35 pm
Nope.
But if this is just because you are lazy, right-click on the table in management studio and generate an insert script.
April 22, 2008 at 2:31 pm
I haven't tried this, but would it be possible to drop the identity column from the temp table before you do the insert into the primary table?
Theoretically, that might work. I haven't tried it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2008 at 2:48 pm
Thanks guys. I decided to not be lazy and list out each field in the insert statement. I just put a note in the stored proc that if there's an error or some incomplete data, the first thing to look at is the table schema vs the insert list.
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply