Dynamically Create Temp (#) Table from Views... (SQL Server 2008)

[smedvid]

Greetings fellow SQL Server Experts...
I have a challenge where I have a need to dynamically create a Temp Table from a set of (2) views; so I can create an Index on it. I tried to use Schema Binding with the views; but seemed to have issues since the views are complex with many aggregate/custom functions.

Anyway...
I have (2) different Views that I am attempting to set into a Temp table so I can index a field for performance in a Join.

How can I get the below to work? I've tried many approaches without success! Can it be done?

DECLARE @SQL			NVARCHAR(4000)
DECLARE @CAT			NVARCHAR(4)
DECLARE @Rollup_Flag		VARCHAR(1)

SET @CAT = 'AAAA'
SET @Rollup_Flag = 'Y'   

--Basically (2 Different Views Used based on Flag!) 
IF @CAT = 'AAAA' AND @Rollup_Flag = 'Y'   
        -- Use VIEW 1  
	BEGIN
		SET @SQL = 'SELECT * INTO #Rollup_Primary_AAAA FROM vw_tblCalc_AAAA_Primary'
		EXEC sp_executesql @SQL
-- EXEC (@SQL)
		CREATE CLUSTERED INDEX idx_Primary_AAAA ON #Rollup_Primary_AAAA ([S_Id]) 
	END

ELSE IF @CAT = 'AAAA' AND @Rollup_Flag = 'N'   
        -- Use VIEW 2
	BEGIN
		SET @SQL = 'SELECT * INTO #Rollup_Primary_AAAA FROM vw_tblCalc_AAAA_Primary_S' 
		EXEC sp_executesql @SQL
-- EXEC (@SQL)
		CREATE CLUSTERED INDEX idx_Primary_AAAA ON #Rollup_Primary_RHEQ ([STraunche_Id]) 
	END
	
SELECT COUNT(*) FROM #Rollup_Primary_AAAA

Error is ...
Cannot find the object "#Rollup_Primary_AAAA" because it does not exist or you do not have permissions.

Ugh!!!!

Thanks in Advance (As Always!)



Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[ousoonerjoe]

Correct me if I'm wrong here, but the EXEC sp_executesql @SQL is creating the #TempTable in a separate connection which in turn is causing it to be destroyed as soon as the connection is dropped. Have you tried to create a persistent table or load it into a table variable and then select from those?

A pre-coffee thought.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[smedvid]

mmm... now I remember... I need another WaWa Coffee myself!
Great catch on EXEC sp_executesql! I believe u are correct. I know EXEC (@SQL) certainly did not work either; buit I thought that used the same connection?

I will try using a Table variable; which I've never used before... good thought, even pre-coffee. lol.

Any other thoughts are welcome.

Thanks!


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[smedvid]

And, also I used ##Rollup_Primary_AAAA for global refernce and it worked! The seperate connection u mentioned was the key to jar my memory!

But, I will still see if the Table variable is better for performance; assuming I can index it...

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gmmastros]

mmm... now I remember... I need another WaWa Coffee myself!

While you're there, can you grab me a cup? I like the 20oz. Dark Roast with a splash of Irish Cream and 3 splenda's.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think you can probably do this without using dynamic sql, which would make the code simpler and probably more reliable. You may also want to consider adding another column to the temp table to store the ID that will become the clustered index.

Something like this...

DECLARE @SQL            NVARCHAR(4000)
DECLARE @CAT            NVARCHAR(4)
DECLARE @Rollup_Flag        VARCHAR(1)

SET @CAT = 'AAAA'
SET @Rollup_Flag = 'Y'   

-- Create the table structure but with no data.
SELECT Convert(Int, NULL) As MyId, * 
INTO   #Rollup_Primary_AAAA 
FROM   vw_tblCalc_AAAA_Primary
WHERE  1=0

Insert
INTO   #Rollup_Primary_AAAA 
Select S_Id As MyId, *
FROM   vw_tblCalc_AAAA_Primary
WHERE  @CAT = 'AAAA' 
       And @Rollup_Flag = 'Y'   

Union All 

Insert
INTO   #Rollup_Primary_AAAA 
Select [STraunche_Id], *
FROM   vw_tblCalc_AAAA_Primary_S
WHERE  @CAT = 'AAAA' 
       And @Rollup_Flag = 'N'   

CREATE CLUSTERED INDEX idx_Primary_AAAA ON #Rollup_Primary_AAAA ([MyId]) 
    
SELECT COUNT(*) FROM #Rollup_Primary_AAAA

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

I actually had that idea of a Union SQL myself; but I got fixated on solving the issue I encountered and had the blinders on... From a true normalized approach; your Union is certaily the correct way... but I have other code dependencies and I assumed this project from someone and I am locked into some of the schema; good or bad... at present. One bad side of assuming a project; is that you may not get to redesign the schema in an optimal form... thanks!!!


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...