Ask the Expert

Automatic numbering with a sequence, part 1

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')
);

    Requires Free Membership to View

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.

Continued on page 2.

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: