I have the following SQL statement to insert records into table1 from table2. I am using ROWID from table2 to give each record a unique sequence number. As an alternative, is it possible within the SQL statement to generate a unique number (i.e. 1, 2, 3, ...., i) for each record that is created on table1?
INSERT INTO table1 ( qa_mailing_date_loaded, qa_creation_date, qa_created_by, qa_sequence_number, qa_account_number ) ( SELECT ac_date_loaded, SYSDATE, 'Eric Donald', ROWID, ac_account_number FROM table2 WHERE ac_completion_date BETWEEN TO_DATE ('2002-11-01','yyyy-mm-dd') AND TO_DATE ('2002-11-30','yyyy-mm-dd') );
Use a sequence, which is a database object designed for generating numbers in order.
CREATE SEQUENCE table1_seq START WITH 1;
sequence_name.NEXTVAL can be thought of as a pseudo-column, like ROWNUM. The first time you refer to the table1_seq.NEXTVAL it will have the value 1; the second time it will be 2; etc. Here's one common example of a sequence in use:
INSERT INTO table1 ( qa_mailing_date_loaded, qa_creation_date, qa_created_by, qa_sequence_number, qa_account_number ) ( SELECT ac_date_loaded, SYSDATE, 'Eric Donald', table1_seq.NEXTVAL, ac_account_number FROM table2 WHERE ac_completion_date BETWEEN TO_DATE ('2002-11-01', 'yyyy-mm-dd') AND TO_DATE ('2002-11-30', 'yyyy-mm-dd') );
Like other objects, sequences are visible to other users only if the owner grants them privileges. The SELECT privilege gives users the power to generate new numbers:
GRANT SELECT ON table1_seq TO table1_update;
Here are some of the options you can use in a CREATE SEQUENCE statement. Look up the CREATE SEQUENCE command in a SQL reference manual for more details.
- START WITH n makes the sequence return n (instead of 1) the first time NEXTVAL is used. (Very useful if you want to start using a sequence with a column that already has unique integer values.)
- INCREMENT BY n causes the sequence to count by n instead of 1. If n is negative, the sequence will generate numbers in descending order.
- MINVALUE n1 MAXVALUE n2 CYCLECauses the sequence to recycle numbers between n1 and n2 (like a bakery queue)
- CACHE n generate (but do not assign) numbers in batches of n for faster performance
The options can appear in any order after CREATE SEQUENCE seq_name.
Dig deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.