SQL: INSERT multiple different hardcoded values in one column

Question

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:

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';

you see "hardcoded_value1" and I want to have not only 1 per query, but multiple hardcoded values for col2 without running the queries multiple times for each hardcoded value. — Gamga
– Gamga, Commented Dec 4, 2019 at 17:42

GMB · Accepted Answer · 2019-12-04 17:44:51Z

2

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';

answered Dec 4, 2019 at 17:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gamga Over a year ago

Thanks a lot, this seems to be the thing that I needed. :)

Paul Williams · Accepted Answer · 2019-12-04 17:46:07Z

0

I can't tell from your question what the basis is for the different values. You may be able to use a CASE statement to insert different hardcoded values based on some criteria:

INSERT INTO
  DB.dbo.Table WITH(ROWLOCK, XLOCK) (
    col1,
    col2,
    col3,
    col4
  )
SELECT
  DISTINCT customer_id,
    CASE WHEN Condition1 THEN hardcoded_value1
         WHEN Condition2 THEN hardcoded_value2
         ...
    END,
    constant1,
    constant2
FROM
  DB.dbo.Other_Table
WHERE
  ID = '0';

answered Dec 4, 2019 at 17:46

Paul Williams

17.2k6 gold badges50 silver badges85 bronze badges

Collectives™ on Stack Overflow

SQL: INSERT multiple different hardcoded values in one column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related