One query instead of using multiple Temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

One query instead of using multiple Temp tables

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2013-08-29 : 10:23:19

Hello ,

I have the following query and I would like to combine it into one query instead of using several temp tables.

IF OBJECT_ID('Tempdb..#a') IS NOT NULL DROP TABLE #a

SELECT *
INTO #a
FROM #Web_table w WITH(NOLOCK)
WHERE w.generated_date IS NOT NULL

IF OBJECT_ID('Tempdb..#b') IS NOT NULL DROP TABLE #b

SELECT *
INTO #b
FROM #a a WITH(NOLOCK)
WHERE EXISTS(SELECT r.order_id FROM report r WITH(NOLOCK)
WHERE r.order_id = a.order_id
AND r.type_id in ('SP','TA')
)

IF OBJECT_ID('Tempdb..#c') IS NOT NULL DROP TABLE #c

SELECT *
INTO #c
FROM #a a WITH(NOLOCK)
WHERE NOT EXISTS(SELECT r.order_id FROM report r WITH(NOLOCK)
WHERE r.order_id = a.order_id
AND r.type_id in ('SP','TA')
)
Thank you ,
Petronas

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-29 : 10:36:54

[CODE]

; WITH CTE AS
(SELECT *
FROM #Web_table w WITH(NOLOCK)
WHERE w.generated_date IS NOT NULL ),
CTE1 AS
(SELECT *
FROM CTE a
WHERE EXISTS(SELECT r.order_id FROM report r WITH(NOLOCK)
WHERE r.order_id = a.order_id
AND r.type_id in ('SP','TA')
)
)
SELECT *
FROM CTE1 a
WHERE NOT EXISTS(SELECT r.order_id FROM report r WITH(NOLOCK)
WHERE r.order_id = a.order_id
AND r.type_id in ('SP','TA')
)

[/CODE]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-29 : 12:00:23

What do you want as output? You can't popualte multiple temp tables with one query (without an output clause) and the bottom two queries seem in conflict with each other: EXISTS and NOT EXISTS with the same query.

Here are some links that can help you prepare your question so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources