Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    3

    Unanswered: Using IDENTITY with SELECT INTO

    Hi,
    We are using Sybase ASE 12.5.3 I am trying to create a large table, minimize logging and add an identity column. It takes several queries to create the table. For the first step I use
    SELECT columns, rowid=identity(9) INTO new_table FROM source_table1
    WHERE ...
    This works fine with the values starting at 1. Then I try to add additional rows:
    SELECT columns, rowid=identity(9) INTO EXISTING TABLE new_table FROM
    source_table2 WHERE ...

    The values of the IDENTITY column restart at 1, so now I have 2 rowids=1.
    When I try to remove the rowid from the SQL I get an error stating the number of columns don't match the table number.
    Can anyone help with this?
    THanks,
    Kathy

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Why do you need a rowid?

    For subsequent inserts you can use insert into new_table select ...
    else add a queryid to new_table
    You can then use the combination of queryid and rowid as the unique key

  3. #3
    Join Date
    Sep 2007
    Posts
    3
    Thanks for your response. I am inserting over a million rows with each insert and want to avoid inserts due to logging. The "select into" uses minimal logging. What is a queryid?

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I meant something like this
    Code:
    select qid=1,id=identity(9),name into #t1 from master..sysdatabases
    select qid=2,id=identity(9),srvname into existing table #t1 from master..sysservers
    select qid=3,id=identity(9),name into existing table #t1 from master..sysusers
    
    select * from #t1 order by qid,id
    
    drop table #t1

  5. #5
    Join Date
    Sep 2007
    Posts
    3
    Thanks. Unfortunately our application code requires a single key because the join logic gets too messy with multiple keys.
    Thanks for you help.

Posting Permissions

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