CODEWITHSUNDEEP
sqlsql-server
IAmGroot
IAmGroot

Reputation: 13865

Can you store tables for reuse in a T-SQL statement?

Okay, so I have this T-SQL statement:

SELECT 
    COUNT(DISTINCT RentalNo) as Count 
FROM 
    RoomRental AS rr 
    LEFT JOIN 
        RoomLinks AS r 
        On (r.RoomNo1 = rr.RoomNo OR r.RoomNo2 = rr.RoomNo) 
        AND r.CostCentreNo = rr.CostCentreNo
WHERE Cancelled = 0 
    AND (rr.RoomNo = @RN OR r.RoomNo2 = @RN OR r.RoomNo1 = @RN) 
    AND rr.CostCentreNo = @CCN 
    AND StartDate = @StartDate
    AND DATEADD(minute,0-SetupTime,StartTime) < @EndBlock 
    AND DATEADD(minute,BreakdownTime,EndTime) > @StartBlock)

Is there any way I can store SELECT *, of it, and then quickly (without applying the statement again), get the RentalNos, or apply another AND to the WHERE clause?

Note, this is just an example of my SQL query, I currently have a loop going around, as it goes through all time blocks, (after first querying the first ending).

Basically, there is a lot of similar SQL query repetition. And it would be great If I could store once and manipulate that instead.

Cheers,

Paul

Upvotes: 2

Views: 277

Answers (3)

adamleerich
adamleerich

Reputation: 5875

You can create a temporary table by doing the following

SELECT *
  INTO #temp_table
  FROM etc.

Or specifically, 

SELECT *
  INTO #temp_table
  FROM RoomRental AS rr LEFT JOIN RoomLinks AS r 
    ON (r.RoomNo1 = rr.RoomNo OR r.RoomNo2 = rr.RoomNo) 
   AND r.CostCentreNo = rr.CostCentreNo
 WHERE Cancelled = 0 
   AND (rr.RoomNo = @RN OR r.RoomNo2 = @RN OR r.RoomNo1 = @RN) 
   AND rr.CostCentreNo = @CCN;

Then you can execute all your queries against #temp_table by using 

SELECT COUNT(DISTINCT RentalNo) as Count 
  FROM #temp_table
 WHERE StartDate = @StartDate             
   AND DATEADD(minute,0-SetupTime,StartTime) < @EndBlock 
   AND DATEADD(minute,BreakdownTime,EndTime) > @StartBlock)

for each set of dates that you want to do.

Upvotes: 2

Widor
Widor

Reputation: 13285

Take a look at Temporary Tables (plenty of info on here and the rest of the net).

They can be queried and INSERTED into just like a normal table.

Upvotes: 0

Derek
Derek

Reputation: 23318

Use SELECT INTO and store the results in a temporary table (the # prefix is used for a temporary table)

SELECT yourcols, ...
INTO #table
FROM yourtables...

Then you can reference #table just like any other physically stored table, join to it, etc.

Upvotes: 7

Related Questions