T-SQL: Using INSERT INTO & OUTPUT inside CASE clause

Question

I need to assign a new_id to a field inside a CASE clause.

Here is the example:

INSERT INTO [table1]
   (field1, field2, field3, field4, field5, field6)
SELECT @someID1, field1_from_table2, @someID2, field2_from_table2, 
 CASE 
  WHEN field3_from_table2 IS NULL 
   THEN INSERT INTO [table3] 
        OUTPUT inserted.some_new_id 
        DEFAULT VALUES
  ELSE field3_from_table2
 END
FROM [table2]

The code fragment

INSERT INTO [table3] 
OUTPUT inserted.some_new_id 
DEFAULT VALUES

works fine when used outside of the CASE clause.

The main problem is that I have to assign a new_id obtained from table3 when the field3_from_table2 I'm trying to insert into table1 IS NULL.
table3 is just an ID dispatcher.

Any idea or workaround to this problem?

Thanks in advance.

You cannot have statements inside your CASE - it is only designed to return values (WHEN .... THEN (some value here) ) — marc_s
– marc_s, Commented Nov 11, 2011 at 19:18
@Chorinator, if field3_from_table2 is NULL, is the row to be INSERTed into table1 in your example? — gonsalu
– gonsalu, Commented Nov 11, 2011 at 19:43
@Gonsalu No, is not in the example, but it's a pretty simple table. It only contains one column and it's an identity. I use that table only as an Id dispatcher. — Chorinator
– Chorinator, Commented Nov 11, 2011 at 21:02

aF. · Accepted Answer · 2011-11-11 22:12:21Z

1

As pointed by marc_s, you can't do that using the a case. You can see the correct case syntax here.

To solve your problem the best solution is to use a cursor.

You can see an code example for a sp with a cursor here.

answered Nov 11, 2011 at 22:12

aF.

67k45 gold badges141 silver badges201 bronze badges

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

Comments

gonsalu · Accepted Answer · 2011-11-12 00:48:28Z

Assuming table3 generates sequential integer values, like a IDENTITY property with increments of value 1, you can generate those yourself using ROW_NUMBER(), and then INSERTing the generated values into table3.

But, since you're generating the values yourself, you must lock table3 to prevent changes ocurring to it while the statement runs.

Let's set up a test case.

USE tempdb
GO

IF OBJECT_ID('table1', 'U') IS NOT NULL DROP TABLE table1;
IF OBJECT_ID('table2', 'U') IS NOT NULL DROP TABLE table2;
IF OBJECT_ID('table3', 'U') IS NOT NULL DROP TABLE table3;

CREATE TABLE table1 (
    field1 int,
    field2 int,
    field3 int,
    field4 int,
    field5 int
);

CREATE TABLE table2 (
    field1_from_table2 int,
    field2_from_table2 int
);

CREATE TABLE table3 (
    field1_from_table3 int IDENTITY(1,1)
);

INSERT INTO table2
VALUES (1000, 2000)
     , (1001, NULL);
GO

-- INSERT 20 records to generate some IDENTITY increments.
INSERT INTO table3 DEFAULT VALUES
GO 20

Here's the example code to generate the sequential values.

BEGIN TRANSACTION;

SET IDENTITY_INSERT table3 ON;
GO

DECLARE @someID1 int = 100
      , @someID2 int = 200;
DECLARE @output table (field4 int, field5 int);

-- Lock table3 exclusively to prevent INSERTs that spoil IDENTITY values.
SELECT TOP(0) 1 FROM table3 WITH (HOLDLOCK, TABLOCKX);

-- INSERT into table1, generating sequential integers
-- and saving the output in @output.
INSERT INTO table1
OUTPUT inserted.field4
     , inserted.field5
  INTO @output(field4, field5)
SELECT @someID1
     , field1_from_table2
     , @someID2
     , field2_from_table2
     , CASE
         WHEN field2_from_table2 IS NOT NULL THEN field2_from_table2
         ELSE (ROW_NUMBER() OVER (PARTITION BY field2_from_table2
                                  ORDER BY field2_from_table2))
              + (SELECT MAX(field1_from_table3) FROM table3)
       END
   FROM table2;

-- INSERT generated integer values.
INSERT INTO table3 (field1_from_table3)
SELECT field5
  FROM @output
 WHERE field4 IS NULL;

SET IDENTITY_INSERT table3 OFF;
GO

COMMIT;

SELECT * FROM table1;
SELECT * FROM table2;
SELECT * FROM table3;

Collectives™ on Stack Overflow

T-SQL: Using INSERT INTO & OUTPUT inside CASE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related