SQL - How to make multiple Inserts in a table with the result of a SELECT * of another table in a query

Question

Hello, I would like to know how to make several Inserts in a table with the result of a SELECT of all id of another table table that are not as foreign key in the first table.

I already have the SELECT query with LEFT JOIN:

SELECT id FROM exemplo ex LEFT JOIN outra out ON ex.id = out.exemplo_id where out.exemplo_id IS NULL

This returns all n Ids from the other table that are not in the first one, what I want is to make kind of a repeat structure that will run Inserts n times.

Answer 1

You don’t need this, you can simply select right after the Insert, making it value, below an example:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Just that they have the same arguments case, you can not choose which ones either, so:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

This way the Insert will be carried out automatically.

EDIT

If you want to use only one of the fields just put the other values manually like this:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT 'Valor manual varchar', 5, column3, ...
FROM table1
WHERE condition;

This way only column three will be pulled from the query and the rest will be re-inserted text.