生成报告，显示两个SQL表之间哪个ID匹配 [英] Generate Report that shows which Ids matched between two SQL tables

问题描述

我有我正在插入来自table1的值表2中的SQL查询仅在表行匹配的ID：

I have an SQL query that I am running to insert values from table1 to table2 only where the Ids of the table row match:

UPDATE PT
SET CreditInvoiceAmount = CSV.CreditInvoiceAmount
,CreditInvoiceDate = CSV.CreditInvoiceDate
,CreditInvoiceNumber = CSV.CreditInvoiceNumber
,CreditDeniedDate = CSV.CreditDeniedDate
,CreditDeniedReasonId = CSV.CreditDeniedReasonId
,CreditDeniedNotes = CSV.CreditDeniedNotes
,StatusId = CASE 
                WHEN CSV.CreditInvoiceDate IS NULL
                    AND CSV.CreditDeniedDate IS NOT NULL
                        THEN 7
                ELSE 8
            END
FROM PermanentTable PT
INNER JOIN TemporaryCsvUpload CSV ON PT.Id = CSV.Id

我想说在临时表中的ID的约60％将在永久表匹配的ID。我想产生某种报告中显示的哪个ID匹配的，哪些没有。中，我其实并不重要，显示该信息的格式（HTML表格，Excel工作表，等等）。我不知道我将如何使用查询来获取该信息，所以我可以显示它。感谢您的帮助！

I would say about 60% of the Ids in the temporary table will match the Ids in the permanent table. I would like to generate some kind of report to display which Ids matched and which didn't. The format in which I display that information doesn't really matter (HTML table, excel sheet, whatever). I'm not sure how I would use a query to get that information so I could display it. Thanks for your help!

推荐答案

要显示所有的ID，以及他们是否是在一个或两个表，试试这个;它会返回是在至少一个表ID的列表，带有一个标记，指示它们出现在其中一个或多个表

To show all the IDs, and whether they were in one or both tables, try this; it will return a list of IDs that are in at least one table, with a flag indicating which table or tables they appear in:

Select ISNULL(p.ID, t.ID) as ID
, case when p.ID is not null then 'Y' else 'N' end as InPermanentTable
, case when t.ID is not null then 'Y' else 'N' end as InTemporaryTable
from PermanentTable p
full outer join TemporaryCsvUpload t
on p.ID = t.ID

要从临时表只返回的ID，以表明他们是否在永久表或没有标志一起，用这个：

To return just the IDs from the temporary table, along with a flag indicating whether they were in the permanent table or not, use this:

Select t.ID
, case when p.ID is not null then 'Y' else 'N' end as InPermanentTable
from TemporaryCsvUpload t
left join PermanentTable p
on p.ID = t.ID

这篇关于生成报告，显示两个SQL表之间哪个ID匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！