Insert into Select proble,

makhan1983 · #1 (**permalink**) 08-06-09, 12:16

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.

n_i · #2 (**permalink**) 08-06-09, 13:58

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

Internet Services

Web Development

Digital Marketing

Consumer Tech