afri afri - 10 days ago 7
SQL Question

how can check the existence of record in multiple table

How can i check the existence of

bomItem
field value in three different table before inserting the record to the table. I want to check whether

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`


are equal

If exist in either of the three table its good to create a record in
BOMHEAD
table. The only common field in the above three table is
itemId
field they contains widely different attributes.

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


Sample Data

Table A
100199
100200
100201

Table B.
200199
200200
200201
200202

table C
400199
400200
400201


Now i want to add i want to record to
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

Use EXISTS to check the existence of records in the tables. Here is one way

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) 

Another approach(may not be as efficient as above query. Run it with your real data to check the performance).

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) 
Comments