Post reply

inserting into a table with an identity column

  • April 22, 2008 at 10:44 am

    #189398

    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

    #805688

    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

    #805768

    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

    #805779

    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