Temporary table question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Temporary table question

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-10 : 11:43:58

I use code below to create a temporary table, it works:

select top 0 * into #MyTemp from order
insert into #MyTemp select * from order

But, I need to pass various table. So I change code to use @sql to replace with "from order". It did not work. How to fix it?

select top 0 * into #MyTemp @sql

insert into #MyTemp select * @sql

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-10 : 12:31:30

you cannot use a variable for table name.
write a dynamic query and execute it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 14:16:30

why are doing this in two step. you could simply do

select * into #MyTemp from order

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-10 : 15:29:08

he wants to do

select * into #MyTemp @sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 01:19:31

quote:
Originally posted by rohitkumar

he wants to do

select * into #MyTemp @sql

for that he needs dynamic sql
declare @sql varchar(100),@str varchar(2000)
set @sql='from yourtable'
set @str='select * into #MyTemp '+@sql
EXEC (@str)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-11 : 01:25:25

quote:
Originally posted by visakh16

quote:
Originally posted by rohitkumar

he wants to do

select * into #MyTemp @sql

for that he needs dynamic sql
declare @sql varchar(100),@str varchar(2000)
set @sql='from yourtable'
set @str='select * into #MyTemp '+@sql
EXEC (@str)

And the temp table #MyTemp will be out of scope and not accessble when exec() returns.

So he need to create the temp table first before exec() but since the source table is dynamic, that makes things more interesting

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 02:19:26

quote:
Originally posted by khtan

quote:
Originally posted by visakh16

quote:
Originally posted by rohitkumar

he wants to do

select * into #MyTemp @sql

for that he needs dynamic sql
declare @sql varchar(100),@str varchar(2000)
set @sql='from yourtable'
set @str='select * into #MyTemp '+@sql
EXEC (@str)

And the temp table #MyTemp will be out of scope and not accessble when exec() returns.

So he need to create the temp table first before exec() but since the source table is dynamic, that makes things more interesting

KH
[spoiler]Time is always against us[/spoiler]

yeah...just missed out that

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-14 : 15:34:20

Once I change local temporary table to global (##), everything works fine. @sql as where sentence can pass from code and store procedure.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-14 : 15:36:27

Sun Foster,

Do you understand the differences between # and ## temp tables? Do you realize what issues you might encounter with ## temp tables when two or more people are running this bit of code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-25 : 14:06:46

You are right, Tara Kizer
But, I do not know how to solve my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 14:16:57

quote:
Originally posted by Sun Foster

You are right, Tara Kizer
But, I do not know how to solve my problem.

perhaps you could give a brief overview of the scenario so that we may be able to suggest a better approach.

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-25 : 14:56:26

Thank you for help.
Here are the steps in asp.net program:
1) use a store procedure to create a global temporary table and insert data. Program pass "where" sentence:

set @SQLInsert = 'insert into ##MyTemp select OrderID, OrderCity from MoreOrder ' + @MyWhere

2) create dataset using statement as below:

"select OrderID, OrderDate, OrderCity FROM OrderHistory INNER JOIN ##MyTemp ON OrderHistory.OrderID = ##MyTemp.OrderID AND OrderHistory.City = ##MyTemp.City”

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-25 : 14:58:11

To fix this, get rid of your dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-25 : 15:01:29

Exactly, temporary tables are not cached in dynamic sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources