CODEWITHSUNDEEP
sqlsql-serverjointemp-tables
user853710
user853710

Reputation: 1767

how to capture the data that is outside of the join scope into a tempTBL

Let's say we have the tables A and B, where A is the parent table of B

TableA:

ID | VAL
1  | "foo"
2  |  "bar"

TableB:

ID | aID
1  |  2

OK?

Now lets have an join:

select *
from A
inner join B on a.Id = b.aID

Is there a way to use the INTO keyword to immediately store the failed join record into a temporary table. Something similar using the OUTPUT clause?

I know that it is a bit far fetched, but maybe there is a way I'm not aware of. Pays off to try.

Upvotes: 2

Views: 184

Answers (2)

gofr1
gofr1

Reputation: 15977

CREATE TABLE ##tmp  (
    ID int,
    VAL nvarchar(3),
    IDD int,
    aID int
)

CREATE TABLE ##tmp1 (
    ID int,
    VAL nvarchar(3)
)

;WITH TableA AS (
SELECT *
FROM (VALUES
(1, 'foo'),(2, 'bar')) as t(ID, VAL)
), TableB AS (
SELECT *
FROM (VALUES
(1, 2)) as t(ID, aID)
)

INSERT INTO ##tmp
select  a.ID, 
        a.VAL,
        b.ID AS IDD,
        b.aID 
from TableA a
FULL OUTER JOIN TableB B on a.Id = b.aID

DELETE FROM ##tmp
OUTPUT deleted.ID, deleted.VAL INTO ##tmp1
WHERE IDD IS NULL

Data in ##tmp:

ID          VAL  IDD         aID
----------- ---- ----------- -----------
2           bar  1           2

(1 row(s) affected)

Data in ##tmp1:

ID          VAL
----------- ----
1           foo

(1 row(s) affected)

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Failed join record ? do you mean non matching records ?

select *
from A
left join B on a.Id = b.ID 
where b.ID IS NULL

To store in temporary table , create table structure with required columns from rows retrived in join operation then do 

INSERT INTO #temp
SELECT * from A
left join B on a.Id = b.ID 
where b.ID IS NULL

or if you require all the columns then do select * into

SELECT * INTO #temp from A
left join B on a.Id = b.ID 
where b.ID IS NULL

Upvotes: 1

Related Questions