CODEWITHSUNDEEP
sqlsql-serversql-server-2005
thevan
thevan

Reputation: 10354

Compare table with a table variable in a Stored procedure in SQL Server 2005

How to compare a table against a table variable in a Stored Procedure? Normally to compare the two tables we used this query:

 SELECT * 
 FROM Table A 
 WHERE NOT EXISTS(SELECT * 
                  FROM Table B 
                  WHERE Table A.ID = Table B.ID)

But here, I have Table A and one table variable @Item like Table B.

In this scenario, how to compare Table A and @Item?

I am writing one stored procedure, in that stored procedure I want to compare the existing one table with the table variable which is generated from the front end and passed as a XML dataset to the Stored Procedure... In that, if all the rows in the table variable @Item presents in the existing table then it returns true else false...

Any suggestions please....

Upvotes: 2

Views: 4002

Answers (6)

Anthony Faull
Anthony Faull

Reputation: 17957

If you want to compare entire tables, and the schemas are guaranteed identical you can use EXCEPT.

SELECT *, 'New row' [Edit] FROM [Table A]
EXCEPT
SELECT * FROM @Item

UNION ALL

SELECT *, 'Deleted row' FROM @Item
EXCEPT
SELECT * FROM [Table A]

Upvotes: 1

Pankaj
Pankaj

Reputation: 10105

You should also try below using Joins as well.

SELECT * FROM Table A 
Left Join @Item sub on A.ID = sub.ID
Where sub.ID is Null

Upvotes: 0

Keith
Keith

Reputation: 155702

I've always preferred the left-join syntax when excluding the results of one table:

select a.*
from [table] a
    left outer join @item b
        on a.ID = b.ID
where b.ID is null

I suspect the query plan should come out the same though.

Upvotes: 4

Brett
Brett

Reputation: 4061

If you're using the subquery, you can do the same sort of thing

select *
from tableA
where not in(
 select b.ID
 from @tableB)

the @tableB is just a table value parameter, rather than a temporary table. Its a variable that holds a table in it, so it can be passed between functions.

you might also be able to do something like:

select *
from tableA a
left outer join @tableB b
on a.ID = b.ID
where b.ID IS NULL

Upvotes: 1

garnertb
garnertb

Reputation: 9584

Reference the table variable just like a normal table:

SELECT * FROM Table A 
WHERE NOT EXISTS(SELECT * FROM @ITEM WHERE Table A.ID = @ITEM.ID)

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41559

If works just the same (altough using table aliases will make it easier)

SELECT * 
FROM Table A 
WHERE NOT EXISTS(
    SELECT * 
    FROM @Item sub 
    WHERE Table A.ID = sub.ID
)

Upvotes: 2

Related Questions