CODEWITHSUNDEEP
sqlsql-servert-sqlselectsql-insert
Gamga
Gamga

Reputation: 11

SQL: INSERT multiple different hardcoded values in one column

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

Upvotes: 0

Views: 1704

Answers (2)

Paul Williams
Paul Williams

Reputation: 17030

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

Upvotes: 0

GMB
GMB

Reputation: 222512

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

Upvotes: 2

Related Questions