Reputation: 197
Hello I would like to insert in a table called Data multiple columns from another table called SourceTable and one colum that has a standar value for every row added in Data.
Assume that you have Column1 and Column2 in the table called SourceTable and source_id is precalculated and it will be the same for every row added into Data on this query.
INSERT INTO Data (Columns1, Column2, source_id)
SELECT Column1, Column2
FROM SourceTable
UNION SELECT 2;
I tried this one but is not working, most likely because the SELECT 2 returns only one row.
Upvotes: 2
Views: 2143
Reputation: 8113
Your issue is that you're giving SQL 3 columns to insert 2 values into, if source_id is going to be 2 as your union selects then you'd want something like this;
INSERT INTO Data (Columns1, Column2, source_id)
SELECT Column1, Column2, 2
FROM SourceTable
The number of columns you're inserting needs to match the number of columns that you're inserting to. The way you were doing it would have produced this result;
Column1 Column2 source_id
Value1 Value2
2
but even the union would have failed as the queries that you're unioning need to have the same number of columns.
Upvotes: 6