tSQL比较2#临时表，并将差异插入第3#临时表

Question

我有两个结构相同的表A和B。我需要从表A中获取表B中不存在的每一行，然后将它们插入第三个临时表C中。

每个表有2列需要比较，即Type和Step，其余列为RowID、CreatedDate、CreatedUserID、ModifiedDate和ModifiedUserID，不需要比较。

有没有一条语句可以用来插入INTO#tempC，比较a和B，并使用TSQL(SQL SERVER 2012)插入表B中不存在的FROM a值

Answer 1

SQL 2012具有EXCEPT语句。这是一个微不足道的例子。

CREATE TABLE #TmpA (
Col Varchar(10),
Irrelevant Int );
CREATE TABLE #TmpB (
Col Varchar(10) );
CREATE TABLE #TmpC (
Col Varchar(10) );
INSERT INTO #TmpA 
SELECT 'A', 0 UNION ALL
SELECT 'B', 7 UNION ALL
SELECT 'C', 5 ;
INSERT INTO #TmpB
SELECT 'A' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D';
INSERT INTO #TmpC
SELECT Col FROM #TmpA 
EXCEPT 
SELECT Col FROM #TmpB
SELECT * FROM #TmpC;
DROP TABLE #TmpA, #TmpB, #TmpC;

---Blams批评之后的第二个场景

CREATE TABLE #TmpA (
Col Varchar(10),
Irrelevant Int );
CREATE TABLE #TmpB (
Col Varchar(10) );
CREATE TABLE #TmpC (
Col Varchar(10),
Irrelevant Int );
INSERT INTO #TmpA 
SELECT 'A', 0 UNION ALL
SELECT 'B', 7 UNION ALL
SELECT 'C', 5 ;
INSERT INTO #TmpB
SELECT 'A' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D';
;WITH Exceptions AS (
SELECT Col FROM #TmpA 
EXCEPT 
SELECT Col FROM #TmpB
)
INSERT INTO #TmpC
SELECT A.Col, A.Irrelevant 
FROM #TmpA A
JOIN Exceptions E ON A.Col = E.Col
SELECT * FROM #TmpC;
DROP TABLE #TmpA, #TmpB, #TmpC;

Answer 2

insert into #temp3 (Type, Step, RowID ..)
select #temp1.Type, #temp1.Step, #temp1.RowID 
from #temp1  
left outer join #temp2 
on #temp1.Type = #temp2.Type 
and #temp1.Step = #temp2.Step
where #temp2.Type is null

Answer 3

感谢Aaron Bertrand，这是最简单的方法，这是我的声明：

INSERT INTO #C SELECT WFTypeID, WFStepID FROM #A EXCEPT SELECT WFTypeID, WFStepID FROM #B

感谢其他回复，伙计们，但当你可以在一行中完成时，代码太多了：)