CODEWITHSUNDEEP
sqloracle-database
Luca
Luca

Reputation: 21

How to use INSERT INTO SELECT

CREATE TABLE EMPLOYEES (
    EMPLOYEEID INT NOT NULL,
    DEPARTMENTID INT NOT NULL,
);

CREATE TABLE MANAGERS (
    EMPLOYEEID INT NOT NULL,
    DEPARTMENTID INT NOT NULL,
    ALTER_TIMESTAMP TIMESTAMP NOT NULL
);

I want to insert EMPLOYEES.EMPLOYEEID, EMPLOYEES.DEPARTMENTID, ALTER_TIMESTAMP to MANAGERS table using INSERT INTO SELECT. (ALTER_TIMESTAMP col is current time)

I want this result enter image description here

this is my code

INSERT INTO MANAGERS
(EMPLOYEEID, DEPARTMENTID, ALTER_TIMESTAMP)
SELECT EMPLOYEEID, DEPARTMENTID, to_char(ALTER_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS:FF3')
FROM EMPLOYEES, DUAL;

but "ORA-00904: "DUAL"."ALTER_TIMESTAMP": invalid identifier"

Upvotes: 1

Views: 73

Answers (1)

Viktor Török
Viktor Török

Reputation: 1319

You have no column named "alter_timestamp" in your table, this is the cause of the error message.

Try the next insert statement:

INSERT INTO MANAGERS
(EMPLOYEEID, DEPARTMENTID, ALTER_TIMESTAMP)
SELECT EMPLOYEEID, DEPARTMENTID, CURRENT_TIMESTAMP
FROM EMPLOYEES;

Upvotes: 3

Related Questions