CODEWITHSUNDEEP
sqlsql-servercaseinsert-into
dafna
dafna

Reputation: 973

SQL Server : how to write a CASE query for a different INSERT INTO statements?

The question is referring to a T-SQL query.

There are two tables:

Table_A:

 A_ID | Category | SomeValue .
 -----+----------+-----------.
   0  |    1     |   ...     .
   1  |    1     |   ...     .
   2  |    2     |   ...     .
   2  |    2     |   ...     .

and Table_B that should get filled by the Category condition of Table_A. Depending on the categories of Table_A I would like to write some data into column 1 OR column 2 of a second table. The other column shall remain null:

 B_ID | cat_x    | cat_y     .
 -----+----------+-----------.
   0  | someval  |   null    . //Category was 1
   1  |  null    |  someval  . //Category was 2
   2  |  null    |  someval  . //Category was 2
   2  | someval  |   null    . //Category was 1

My input is a whole table. For every row it has to look up the goal column to write in.

I tried to work with CASE, but I didn't got it. Here is some pseudo code that might help you to understand:

CASE
   WHEN Category=1
      THEN 
         INSERT INTO (B_ID,cat_x)
             SELECT (someval) //ID could get filled automatically
             FROM sometable
   WHEN Category = 2
      THEN 
         INSERT INTO (B_ID,cat_y)
             SELECT (someval) //ID could get filled automatically
             FROM sometable
END

My biggest problem I guess is how to write the syntax for the condition/when-clause.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Not quite. The case expression should be part of the `select query:

INSERT INTO (B_ID, cat_x, cat_y)
        SELECT (case when a.category = 1 then someval end),
               (case when a.category = 2 then someval end)
        FROM A
        WHERE category IN (1, 2);

Upvotes: 1

Related Questions