CODEWITHSUNDEEP
sql-servert-sql
Sperick
Sperick

Reputation: 2761

SQL: Can this be written more efficiently?

I want to create a temp table to hold unique gid values from a number of different tables that all share this common gid. I currently have this written as follows:

CREATE TABLE #QHP_Unique
(some_gid INT)  

INSERT INTO #QHP_Unique
SELECT DISTINCT PR.some_gid
FROM Table_PR PR

INSERT INTO #QHP_Unique
SELECT DISTINCT PX.some_gid
FROM Table_PX PX
LEFT OUTER JOIN #QHP_Unique QS ON PX.some_gid= QS.some_gid
WHERE QS.some_gid IS NULL;

INSERT INTO #QHP_Unique
SELECT DISTINCT COP.some_gid
FROM Table_COP COP
LEFT OUTER JOIN #QHP_Unique QS ON COP.some_gid= QS.some_gid
WHERE QS.some_gid IS NULL;

and 3 more of these INSERTS with 3 other tables.

Is there a way to write all of this in a single query?

Upvotes: 2

Views: 69

Answers (1)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You can do it using UNION, it will pick DISTINCT values from combined tables:

CREATE TABLE #QHP_Unique
(some_gid INT)  

INSERT INTO #QHP_Unique (some_gid)
SELECT some_gid
FROM Table_PR

UNION 

SELECT some_gid
FROM Table_PX 

UNION 

SELECT some_gid
FROM Table_COP

Upvotes: 4

Related Questions