如何检查多个表中的记录是否存在

Question

如何在将记录插入表之前检查三个不同表中是否存在bomItem字段值。我要检查

1. `bomitem` of `BOMHEAD` Table with `itemId` of `Table A` 
     or 
    2. `bomitem` of `BOMHEAD` Table with `itemId` of `Table B` 
     or 
    3. `bomitem` of `BOMHEAD` Table with `itemId` of `Table C` 

是否相等

如果存在无论是在三代表其良好的BOMHEAD表中创建一个记录。上述三张表中唯一的共同字段是itemId字段，它们包含广泛不同的属性。

SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[BOMHEAD](
    [bomItem] [int] NOT NULL, 
    [bomRev] [nvarchar](6) NOT NULL, 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

GO 

样本数据

Table A 
100199 
100200 
100201 

Table B. 
200199 
200200 
200201 
200202 

table C 
400199 
400200 
400201 

现在，我想补充我要录制BOMHEAD

INSERT INTO BOMHEAD (bomItem) VALUES (400199); //OK since exist in Table C 
INSERT INTO BOMHEAD (bomItem) VALUES (200202); //OK since exist in Table B 
INSERT INTO BOMHEAD (bomItem) VALUES (500202); //NO because doesnt exist in either A or B or C tables 

Answer 1

使用EXISTS检查记录是否存在的表格中。这里有一种方法

INSERT INTO BOMHEAD(bomItem) 
SELECT bomItem 
FROM (VALUES (400199), 
       (200202), 
       (200202))tc(bomItem) 
WHERE EXISTS (SELECT 1 FROM TableA A WHERE tc.bomItem = A.itemId) 
     OR EXISTS (SELECT 1 FROM TableB B WHERE tc.bomItem = B.itemId) 
     OR EXISTS (SELECT 1 FROM TableC C WHERE tc.bomItem = C.itemId) 

另一种方法（可能不如上面的查询效率高。与你的真实数据运行它来检查的性能）。

INSERT INTO BOMHEAD 
      (bomItem) 
SELECT bomItem 
FROM (VALUES (400199), 
       (200202), 
       (200202))tc(bomItem) 
WHERE EXISTS (SELECT 1 
       FROM (SELECT itemId FROM TableA A 
         UNION ALL 
         SELECT itemId FROM TableB B 
         UNION ALL 
         SELECT itemId FROM TableC C) a 
       WHERE a.itemId = tc.bomItem)