I could not find a solution for this yet. I want to insert multiple rows with 2 or more different hardcoded values, but also with data that I get from another table.
Example: I want to add 2 items into a table for a user that has the ID = ‘0’ in another table without running 2 queries.
This is what I’ve done so far:
x
INSERT INTO
DB.dbo.Table WITH(ROWLOCK, XLOCK) (
col1,
col2,
col3,
col4
)
SELECT
DISTINCT customer_id,
hardcoded_value1,
constant1,
constant2
FROM
DB.dbo.Other_Table
WHERE
ID = '0';
Advertisement
Answer
You could cross join
your select
query with a table value constructor that holds several records with harcoded values. This will generate as many rows as provided in the table value constructor for each row return by the query.
INSERT INTO
DB.dbo.Table WITH(ROWLOCK, XLOCK) (
col1,
col2,
col3,
col4
)
SELECT
DISTINCT t.customer_id,
x.hardcoded_value,
t.constant1,
t.onstant2
FROM DB.dbo.Other_Table t
CROSS JOIN (VALUES ('harcoded 1'), ('harcoded 2')) as x(hardcoded_value)
WHERE t.ID = '0';