Creating Temporary tables dynamically

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

Creating Temporary tables dynamically

Author

Topic

mervyn_p
Starting Member

2 Posts

Posted - 2009-10-08 : 01:20:51

Hi,

Is it possible to create a temporary table dynamically in Stored Procedures in SQL server 2005? I have been trying this for last two days but am getting an exception.

"Msg 1088, Level 16, State 12, Procedure proc_FactUpdate, Line 35
Cannot find the object "#temp_factupdate" because it does not exist or you do not have permissions."

If we can create please provide an example.

Below is my query:

SET @sql = '
select dcl.ClientKey, dcl.Contact, dcl.StateId, dcl.LocId, efu.ClientKeyOld
into tempdb..#temp_factupdate
from [' + @param + '].dbo.Client dcl,
dbo.Employee efu
where delete_dt = null'

exec(@sql)

CREATE INDEX IX_FACTUPDATE1 ON tempdb..#temp_factupdate(ContactId,StateId,LocId)

CREATE INDEX IX_FACTUPDATE2 ON tempdb..#temp_factupdate(ContactId,StateId,LocId,ClientKeyOld)

Thanks,
Mervyn

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-08 : 01:37:22

Hi

Temp table active only within the dynamic query

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 04:08:32

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

mervyn_p
Starting Member

2 Posts

Posted - 2009-10-08 : 04:11:22

quote:
Originally posted by madhivanan

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

I want to pass the database name as a parameter as it should be configurable to run from different environments

Thanks,
Mervyn

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-08 : 04:16:03


IF you want the same logic u can try this....

CREATE TABLE #temp_factupdate(ClientKey VARCHAR(50),Contact VARCHAR(50),
StateId VARCHAR(50),LocId VARCHAR(50),ClientKeyOld VARCHAR(50))


SET @sql = '
select dcl.ClientKey, dcl.Contact, dcl.StateId, dcl.LocId, efu.ClientKeyOld
from [' + @param + '].dbo.Client dcl,
dbo.Employee efu
where delete_dt = null'--This will work when SET ANSI_NULLS OFF only

INSERT INTO #temp_factupdate
exec(@sql) 


DROP TABLE #TEMP

-------------------------
R...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-10-08 : 08:23:39

Yuk! This is really nasty.
Have you considered creating views of selects on the various databases and incorporate the DB name in the view. You can then create a query (or another view) combining all of them and you can write a more traditional non-dynamic query.
Better still, ditch the various DBs and have it all in one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources