TSQL: How do i detect and insert missing records

问题:

I have T-SQL Table below.

 ID   Cost    MaxCost
 -------------------------------
 2    200     300
 3    400     1000
 6    20      100

The above table must have 10 rows with IDs 1 to 10. So its missing 7 rows. How do i insert missing rows with proper ID. The cost & maxcost for missing rows will be zero. Do i need to create a temp table that holds 1 to 10 numbers?

回答1:

No need for temp table, simple tally derived table and LEFT OUTER JOIN are sufficient:

CREATE TABLE #tab(ID INT, Cost INT, MaxCost INT);

INSERT INTO #tab(ID, Cost, MaxCost)
VALUES (2, 200,300),(3, 400, 1000) ,(6, 20, 100);

DECLARE @range_start INT = 1
       ,@range_end INT = 10;

;WITH tally AS
(
  SELECT TOP 1000 r = ROW_NUMBER() OVER (ORDER BY name)
  FROM master..spt_values
)
INSERT INTO #tab(id, Cost, MaxCost)
SELECT t.r, 0, 0
FROM tally t
LEFT JOIN #tab c
  ON t.r = c.ID
WHERE t.r BETWEEN @range_start AND @range_end
  AND c.ID IS NULL;

SELECT *
FROM #tab
ORDER BY ID;

LiveDemo

EDIT:

Tally table is simply number table. There are many ways to achieve it with subquery:

• recursive cte
• ROW_NUMBER() from system table that holds many values (used here)
• UNION ALL and CROSS JOIN
• VALUES(...)
• using OPENJSON (SQL Server 2016+)
• ...

The TOP 1000 will generate only 1000 records if you know that you need more you can use:

SELECT TOP 1000000 r = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM master..spt_values c
CROSS JOIN master..spt_values c2;

回答2:

Since your number of rows is low you could just define the data explicitly...

CREATE TABLE Data(ID INT, Cost INT, MaxCost INT);

INSERT INTO Data(ID, Cost, MaxCost) VALUES(2, 200, 300);
INSERT INTO Data(ID, Cost, MaxCost) VALUES(3, 400, 1000);
INSERT INTO Data(ID, Cost, MaxCost) VALUES(6, 20, 100);

and the query...

select *
from (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) RowNums (ID)
left outer join Data on RowNums.ID = Data.ID

The first part defines a column ID with rows 1-10, it then left outer joins to your data. The beauty of this is that it is very readable.

回答3:

I like to google for new and better ways to do things.. so i stumbled over this post and...Well what worked good in SQL7 and works good in SQL2016 is to just use a outer join and look for NULL values(null is missing data) ....

insert into DestTable (keyCol1,col1,col2,col3...)
select keyCol1,col1,col2,col3,...)
  from SouceTable as s
  left outer join DestTable as d on d.KeyCol1=s.KeyCol1
 where d.KeyCol1 is null
   and ...

feel free to test it wrap your statement in a transaction, delete a few rows and see them come back in the select statement that would normally insert the rows in the destination table...

BEGIN TRAN
--> delete a subset of the data, in this case 5 rows
set rowcount 5;

-->delete and show what is deleted
delete from DestTable;
OUTPUT deleted.*,'DELETD' as [Action]

--> Perform the select to see if the selected rows that are retured match the deleted rows
--insert into DestTable (keyCol1,col1,col2,col3...)
Select keyCol1,col1,col2,col3,...)
from SouceTable as s
left outer join DestTable as d on d.KeyCol1=s.KeyCol1
where d.KeyCol1 is null
and ...

ROLLBACK  

another way would be a TSQL merge, google that if you need to also update and optionally delete...