INSERT INTO with subquery and text value at once

April 20, 2022

I want to put data to employees table by inserting data in this way:

insert into employees(lastName, firstName, extension, email, officeCode, jobTitle)
    select contactLastName, contactFirstName 
    from customers 
    where country like 'usa'
    union all
    select officeCode 
    from offices 
    where country like 'USA' 
    limit 1;

But extension, email and JobTitle doesn’t exist in customers and offices.
Is there any option to make INSERT INTO with subquery and text values for those 3 columns?

>Solution :

Should be able exclude the columns you don’t want as long as they aren’t required fields and then you can join to your offices table.

INSERT INTO employees (lastName, firstName, officeCode, extension, email, jobTitle)
SELECT contactLastName, contactFirstName, officeCode, ‘999’, ‘email@email.com’, ‘Manager’
FROM   customers
       LEFT JOIN offices ON offices.country = customers.country
WHERE  customers.country = 'USA'