I have the following SQL statement to insert records into table1 from table2. I am using ROWID from table2 to give...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.