Surrogate Key in Netezza - SSIS and Netezza sequence

Question

I am fetching some data from an Oracle table and inserting into a Netezza table using an SSIS Script Task in Control Flow.

Objective: to populate a surrogate key (like a Landing ID) field in the destination table.

For now the query looks like:

insert into destination_table
(
id, col1, col2, col3
)
select (SELECT NEXT VALUE FOR MY_SEQUENCER), field1, field2, field3
from source_table

I am using a Netezza sequence (MY_SEQUENCER) to generate a unique number but the sequence value doesn't get incremented until the query commits. As a result, after the data is inserted, I am getting:

1, val1, val2, val3
1, val4, val5, val6
...

subsequent run:

2, val1, val2, val3
2, val4, val5, val6
...

and so on.

Is there a way to achieve this without using a sequence?

Thanks a lot for reading and any ideas/tips/suggestions you may provide.

Answer 1

I saw the resolution from here ... In Netezza I'm trying to use a sequence in a case statement but the sequence value doesn't increment

insert into destination_table
(
id, col1, col2, col3
)
select (NEXT VALUE FOR MY_SEQUENCER), field1, field2, field3
from source_table

Answer 2

Remove the parentheses and redundant select and it will generate a new value for every row.

TESTDB.ADMIN(ADMIN)=> select (select next value for mysequence), col1, col2 from test2 limit 5;
 ?COLUMN? | COL1 | COL2 
----------+------+------
        2 |    0 | 0
        2 |    2 | 2
        2 |    4 | 4
        2 |    6 | 6
        2 |    8 | 8
(5 rows)

TESTDB.ADMIN(ADMIN)=> select next value for mysequence, col1, col2 from test2 limit 5;
 NEXTVAL | COL1 | COL2 
---------+------+------
  200001 |    1 | 1
  200002 |    3 | 3
  200003 |    5 | 5
  200004 |    7 | 7
  200005 |    9 | 9
(5 rows)