CODEWITHSUNDEEP
sqloracle-database
wholly_cow
wholly_cow

Reputation: 977

Creating an Oracle SQL Table from another table and an identity column

I usually use this code to create a new table from existing tables:

CREATE TABLE TABLE_NEW
AS
SELECT 'TEST' AS TEST_COLUMN,
        PRS_ID AS TABLE_ID
FROM TABLE_OLD

How do I insert an identity column into TABLE_NEW. So I will have 3 columns in TABLE_NEW, ID, TEST_COLUMN and TABLE_ID?

ID could be defined as

[ID] [int] IDENTITY(1,1) NOT NULL,

Thanks!

Upvotes: 0

Views: 68

Answers (1)

Peter Pei Guo
Peter Pei Guo

Reputation: 7870

Create the table first.

Use a sequence, and you can create one like this:

CREATE SEQUENCE myseq
START WITH     1
INCREMENT BY   1
NOCACHE
NOCYCLE;

Then use:

INSERT INTO table2 (col1, col2, col3) 
SELECT myseq.nextval, foo1, foo2 
FROM table1

Upvotes: 1

Related Questions