Keeping ROWNUM across two SELECT statements

I'm trying to load a table which will have a column that uniquely identifies the rows. I'm not allowed to use Stored Procedures or Triggers (because of how we deploy our product).

I know I can do:

INSERT INTO Results_Table
SELECT ROWNUM, Val1, Val2 FROM Table1;

But would like to then have the SELECT from Table2 start at one greater than the next ROWNUM.

I've tried:

CREATE SEQUENCE my_sequence;
INSERT INTO Results_Table
SELECT my_sequence.NEXTVAL, Val1, Val2 FROM Table2;

Only to be told:

ORA-02287: sequence number not allowed here

I've tried:

DECLARE @CNT NUMBER(5,0);
SELECT @CNT = COUNT(*) FROM Results_Table; INSERT INTO Results_Table
SELECT ROWNUM + @CNT, Val1, Val2 FROM Table2;

But this is clearly SQL Server syntax, and Oracle throws up on the "@" sign. I've seen posts on creating a Trigger on the table and using a SEQUENCE that way, but as I say, I'm not allowed to use triggers.

Anyone? Received on Fri Sep 09 2005 - 12:17:15 CDT