Insert Into w/ indentity col

  • Hopefully this is easy.

    I have a table with 50+ cols. The primary key is an identity col (autokey).

    What is the easiest way to copy a specific existing row and append it?

    I tried:

    Insert into TableName

    Select * from TableName

    where autokey = 123

    Running this, however, gives me an error related to the identity col - basically saying you can't supply the identity value.

    I believe I could list out all 50+ cols - omitting the the identity col - but I was hoping for something a little cleaner & simpler.

    Any thoughts? Thanks.

  • Nothing easier. Need to include the columns.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • But at least you can get Query analyser to help you with listing the columns (if you're using 2000) by right clicking and scripting to knew window the insert. You'll need to chop and change it a bit but it helps.

    Alternatively (and probably better)

    try this which you can structure as you wish

    --#############################3

    declare @sql1 varchar(1000)

    declare @tableName varchar(50)

    set @tableName = '<tablename>'

    set @sql1 = ''

    Select @sql1 = @sql1 + c.[name] + ', '

    from

    sysobjects o

    inner join

    syscolumns c

    on o.id = c.id

    where

    o.name = @tableName

    order by colorder

    Select @sql1

    --#############################3

  • Thanks for the quick responses.

    I will give it a try.

    I appreciate the help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply