CODEWITHSUNDEEP
sql-servert-sql
aragorn
aragorn

Reputation: 197

MSSQL Insert Data From One Table To Another With Fixed Value

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

Answers (1)

Rich Benner
Rich Benner

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

Related Questions