SQL Server Add missing rows to a table using another table

egdjgwm8 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(138)

I have a table rep.Ref containing Date , Country and Total_Amt . I have another table rep.Customer containing Date, Country , Customer and Amt . If a combination of Date and Country does not exist in rep.Customer for a Customer , then I want to insert a row into rep.Customer and set Amt = NULL for that row. I need to do this for each Customer where the combination of Date and Country does not exist in rep.Customer .

I have the below code but it does not return any rows to be inserted unless I apply filters for the specific country and date that I know are missing (I've commented these out in the code below). And even if I apply the filters the Customer is returned as NULL .

INSERT INTO rep.Customer (Date, Country, Customer, Amt)
SELECT oa.Date, oa.Country, dca.Customer, NULL
FROM rep.Ref oa
--FROM (select * from rep.Ref where Dest_Country = 'Chile' and Date = '2018-12-31') oa
LEFT JOIN (
    SELECT DISTINCT Date, Country, Customer
    FROM rep.Customer
    --where Date = '2018-12-31' and Country = 'Chile' and Customer = 'ABC'
) dca ON oa.Date = dca.Date
    AND oa.Country= dca.Country
WHERE dca.Customer IS NULL

sql-server

来源：https://stackoverflow.com/questions/76510251/add-missing-rows-to-a-table-using-another-table

1条答案

按热度按时间

5lwkijsr1#

I think you need to separately select DISTINCT Customer and DISTINCT Date, Country and then cross join them before conditionally merging the resulting Customer/Date/Country combinations back into rep.Customer .

Try something like:

INSERT INTO rep.Customer (Date, Country, Customer, Amt)
SELECT dc.Date, dc.Country, c.Customer, NULL
FROM (
    SELECT DISTINCT Date, Country
    FROM rep.Ref
) dc
CROSS JOIN (
    SELECT DISTINCT Customer
    FROM rep.Customer
) c
WHERE NOT EXISTS (
    SELECT *
    FROM rep.Customer c2
    WHERE c2.Date = dc.Date
    AND c2.Country = dc.Country
    AND c2.Customer = c.Customer
)

The above uses NOT EXISTS(...) , but a LEFT JOIN ... WHERE ... IS NULL can be used also.

LEFT JOIN rep.Customer c2
    ON c2.Date = dc.Customer
    AND c2.Country = dc.Customer
    AND c2.Customer = c.Customer
WHERE c2.Customer IS NULL

See this db<>fiddle for a sample execution.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Add missing rows to a table using another table

1条答案

相关问题

热门标签

最新问答