Insert into temporary table from select returns blank table-CodePudding

I'm trying to fill two temporary tables with ids coming from outside as a single string that I split and save to a third temporary table:

CREATE TABLE #TempProdotti (Id int NULL);
CREATE TABLE #TempProdottiAggregati (Id int NULL);
CREATE TABLE #TempCorsiSingoli (Id int NULL);

-- split ids and cast them as INT
INSERT INTO #TempProdotti (Id)
(SELECT CAST(value AS int) AS Id
 FROM string_split('3116,3122,3090', ','));

-- then search into products table if the ids match any aggregated (or not) product.
-- then save aggegated products id in one table and the not aggregated ones into another

INSERT INTO #TempCorsiSingoli (Id)
(SELECT Id
 FROM mod_SHOP_Prodotti
 WHERE Id IN (SELECT Id FROM #TempProdotti)
   AND ProdottoAggregato = 0);

INSERT INTO #TempProdottiAggregati (Id)
(SELECT Id
 FROM mod_SHOP_Prodotti
 WHERE Id IN (SELECT Id FROM #TempProdotti)
   AND ProdottoAggregato = 1);



SELECT *
FROM #TempProdotti;
SELECT *
FROM #TempProdottiAggregati;
SELECT *
FROM #TempCorsiSingoli;

DROP TABLE #TempProdotti;
DROP TABLE #TempProdottiAggregati;
DROP TABLE #TempCorsiSingoli;

When I run the query, if it doesn't find anything in one of the two temporary tables, it just returns an empty table:

Is there a clean way to return NULL on Id in case the condition is not met?

CodePudding user response：

One method would be to LEFT JOIN from a data set with row of NULL values:

SELECT TP.*
FROM (VALUES(NULL))V(N)
     LEFT JOIN #TempProdotti TP ON 1 = 1;

If #TempProdotti contains rows, then the data in the table will be returned. If not a single row, of NULLs will be returned.

CREATE TABLE #TempProdotti (Id int NULL);
CREATE TABLE #TempProdottiAggregati (Id int NULL);
CREATE TABLE #TempCorsiSingoli (Id int NULL);
GO

INSERT INTO #TempProdotti (Id)
SELECT CAST(value AS int) AS Id
FROM string_split('3116,3122,3090', ',');

INSERT INTO #TempCorsiSingoli (Id)
SELECT Id
FROM mod_SHOP_Prodotti
WHERE Id IN (SELECT Id FROM #TempProdotti)
  AND ProdottoAggregato = 0;

INSERT INTO #TempProdottiAggregati (Id)
SELECT Id
FROM mod_SHOP_Prodotti
WHERE Id IN (SELECT Id FROM #TempProdotti)
  AND ProdottoAggregato = 1;

GO

SELECT TP.*
FROM (VALUES (NULL)) V (N)
     LEFT JOIN #TempProdotti TP ON 1 = 1;
SELECT TPA.*
FROM (VALUES (NULL)) V (N)
     LEFT JOIN #TempProdottiAggregati TPA ON 1 = 1;
SELECT TCS.*
FROM (VALUES (NULL)) V (N)
     LEFT JOIN #TempCorsiSingoli TCS ON 1 = 1;
GO
DROP TABLE #TempProdotti;
DROP TABLE #TempProdottiAggregati;
DROP TABLE #TempCorsiSingoli;