Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    27

    Unhappy Unanswered: Getting rid of the identity column

    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.

    Any ideas would be appreciated.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    you need to set the identity _insert on and use a column list

    insert tbl (col1, col2, col3, ...)
    select col1, col2, col3, ... from tbl2

    fortunately this is quite easy

    declare @s varchar(4000)
    select @s = coalesce(@s + ',','') + name
    from syscolumns
    where id = object_id(@tblname)

    this will give the column list - just use it in the insert and select.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •