SQL CASE statement ELSE block usage for inserting a record

Question

The last line of the following code inserts records from #S to #D if value is a valid date; otherwise it returns NULL (since I am not using ELSE clause):

CREATE TABLE #S(COL VARCHAR(15))
INSERT #S VALUES('20250630'),('ABC'),('20150821')
CREATE TABLE #E(ERCOL VARCHAR(15))
CREATE TABLE #D(DTCOL DATE)
INSERT #D(DTCOL) SELECT (CASE WHEN ISDATE(COL) = 1 THEN (CAST(COL AS DATE)) END) FROM #S

Question: How can we include ELSE block in the last line of above code and use that block to insert the error value (ABC) into #E table while still using the same SELECT statement as above. So, we are just modifying the last line of the above code:

INSERT #D(DTCOL) SELECT (CASE WHEN ISDATE(COL) = 1 THEN (CAST(COL AS DATE)) ELSE 'NEED TO INSERT "ABC" VALUE TO #E TABLE' END) FROM #S

• Yano_of_Queenscastle: Is it requirement to do in single step? If so, why? Or would two step solution be good enough?
• nam: @Yano_of_Queenscastle Not a requirement but my own interest/curiosities. At least something very close to this approach with inserting 500,000 records while inserting failed records in another table at the same time. I'm aware of other approaches (using IIS, Bulk Insert etc.) but more interested in something closer to this code.
• Craig: Of course, that's not how CASE works - when SELECTed, it's purpose is to condition the output value of a column, not to allow some database action to occur. With SQL Server, it's not possible to insert two separate sets of data into two separate tables with a single INSERT statement - you will need two INSERTs. The first INSERT will select from #S WHERE ISDATE(COL) = 1, and the second INSERT will select from #S WHERE ISDATE(COL) = 0. I know you say "inserting failed records" in your comment, but you're not really talking about "failures", it's just records that meet different criteria
• siggemannen: You can use a insert merge statement to do this, but it's usually not a good idea because the code becomes complicated. Easiest is to just do: delete t output deleted.* into #E from #source t where t.SomeValue is wrong and insert the correct values later

Answer 1

Mostly

You can use this code to Do What You're AskingTM️, with a couple caveats. The first being that "bad" rows will be inserted with a canary value of 17530101, and the second being that those rows will be deleted on subsequent executions of the MERGE statement.

This should answer your question about doing it all in one statement via Composable DML©️, but it may be dissatisfying based on the caveats above.

If so, you'll want to break this up into two steps as suggested in various comments.

INSERT
 #E(ERCOL)
SELECT
 x.COL
FROM 
(
 MERGE 
 #D WITH (SERIALIZABLE) AS t
 USING
 (
 SELECT
 s.*,
 m = 
 CASE 
 WHEN TRY_CAST(s.COL AS date) IS NOT NULL 
 THEN s.COL 
 ELSE CONVERT(date, '17530101') 
 END
 FROM #S AS s
 ) AS s
 ON s.m = CONVERT(date, '17530101') 
 WHEN NOT MATCHED
 THEN 
 INSERT
 (DTCOL)
 VALUES
 (s.m)
 WHEN MATCHED
 AND t.DTCOL = CONVERT(date, '17530101') 
 THEN DELETE
 OUTPUT
 $action AS a,
 s.*
) AS x
WHERE x.m = CONVERT(date, '17530101');

• Yano_of_Queenscastle: Why the SERIALIZABLE hint?