Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Question Unanswered: Insert into Select proble,

    Hi guys,
    I am trying to insert records from a table. The records are for year 2009 and i need to copy the same records with year 2010 in it. THe catch here is the primary key column should be incremented from the max value from 2009. I wrote this query but I get an error.
    The table has 3 colums app_id, year and timestamp

    INSERT INTO SYS_APP
    SELECT MAX(APP_ID)+1 FROM SYS_APP WHERE YEAR IN ('2009','2010')
    SELECT '2010'
    ,CURRENT TIMESTAMP
    FROM SYS_APP WHERE YEAR = '2009'

    Please help.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think the best option would be to alter your APP_ID column to make it an IDENTITY, generated by default. Barring that, you could also try something like this:
    Code:
    insert into sys_app (app_id, year, ts) 
    select m+s, '2010', current_timestamp from (
     select max(app_id) over() m, row_number() over() s 
     from sys_app
     where year='2009'
    ) t
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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