CODEWITHSUNDEEP
sqloracle-database
Harlan
Harlan

Reputation: 189

Create a new table from existing table with specific columns and a sequence number (Oracle SQL)

I'm trying to figure out how to create a table in Oracle SQL from an existing table. I only want to use certain columns, and I want to add a column to the table that is a sequential number.
The original table was created with:

CREATE TABLE EMPLOYEE 
  (
    FNAME VARCHAR2(15)  NOT NULL,
  MINIT CHAR,
  LNAME VARCHAR2(15)    NOT NULL,
  SSN CHAR(9) NOT NULL,
  BDATE DATE,
  ADDRESS VARCHAR2(30),
  SEX CHAR,
  SALARY NUMBER(10,2),
    SUPER_SSN CHAR(9),
  DNO NUMBER NOT NULL,
  CONSTRAINT EMPPK PRIMARY KEY (SSN) DISABLE,
  CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPER_SSN) REFERENCES EMPLOYEE(SSN) DISABLE
  );

I want to use the FNAME, LNAME, SSN and BDATE columns. I also want to add a new column, EMP_NUM which is a sequential number starting at 1000.

I was trying:

CREATE 
TABLE NEW_EMP
  (EMP_NUM NUMBER)
AS 
SELECT SSN, FNAME, LNAME, BDATE
  FROM EMPLOYEE

But kept getting an error. Every time I think I've got it, I get an error. Any help is appreciated.
Thanks

Upvotes: 0

Views: 648

Answers (2)

user330315
user330315

Reputation:

To populate the emp number from a sequence you need to first create a sequence, then use it in the select statement:

create sequence seq_emp_num start with 1000;

CREATE TABLE NEW_EMP
AS 
SELECT seq_emp_num.nextval as emp_num, SSN, FNAME, LNAME, BDATE
FROM EMPLOYEE;

For more details see the manual:

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You could do something like this:

CREATE TABLE NEW_EMP AS 
SELECT ROW_NUMBER() OVER (ORDER BY EMPPK) + 999 as EMP_NUM,
       SSN, FNAME, LNAME, BDATE
FROM EMPLOYEE;

I would be careful about doing this, though. Your original table has a primary key, so I think that should follow each row around everywhere. In addition, I see little use for such a table. You can always generate it easily when you need it, for instance, by using a CTE in a query.

Upvotes: 0

Related Questions