Inserting data into table with select-CodePudding

i`m trying to do something.... I have to insert some data in to a table but..... So here is where I end up...

INSERT into HR.my_employees
(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
  SELECT 
    3 AS ID, 
    'Biri' AS LAST_NAME, 
    'Ben' AS FIRST_NAME,
    substr(FIRST_NAME,1,1)||''||substr(LAST_NAME,1,7) AS userid, 
    1100 AS salary
FROM dual
UNION
  SELECT 
    4 AS ID, 
    'Newman' AS LAST_NAME, 
    'Chad' AS FIRST_NAME,
    substr(FIRST_NAME,1,1)||''||substr(LAST_NAME,1,7) AS userid, 
    750 AS salary
FROM dual;

any suggestion...

CodePudding user response：

You cannot refer to an alias in the SELECT or WHERE clauses of a sub-query where it is defined. Generate the data in a sub-query (or a sub-query factoring clause) and then refer to it in an outer query:

INSERT into HR.my_employees(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
WITH data (id, last_name, first_name, salary) AS (
  SELECT 3, 'Biri', 'Ben', 1100 FROM DUAL UNION ALL
  SELECT 4, 'Newman', 'Chad', 750 FROM DUAL
)
SELECT id,
       last_name,
       first_name,
       SUBSTR(first_name,1,1) || SUBSTR(last_name,1,7),
       salary
FROM   data;

or:

INSERT into HR.my_employees(ID,LAST_NAME,FIRST_NAME,userid,SALARY)
SELECT id,
       last_name,
       first_name,
       SUBSTR(first_name,1,1) || SUBSTR(last_name,1,7),
       salary
FROM   (
  SELECT 3 AS id, 'Biri' AS last_name, 'Ben' AS first_name, 1100 AS salary FROM DUAL
UNION ALL
  SELECT 4, 'Newman', 'Chad', 750 FROM DUAL
);

fiddle

CodePudding user response：

Why do you want to use select statements? - Based on the values you provide it seems like you want to insert hardcoded values. Or does the dual table contain data you want to insert into HR.my_employees?

If you want to just insert values into the table, but not from an existing one, you can use the following structure:

INSERT INTO table_name (column1, column2, column3, ..., columnXX)
VALUES (value1, value2, value3, ..., valueXX);