Use temp out of the dynamic statement

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)

Use temp out of the dynamic statement

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-09 : 08:30:05

Hi all,

I create a temp table in a dynamic statement.
and I need to use the temp table for futher.
How to use the temp table out of that scope.

Sample
------
if object_id('tempdb..#a') is not null
drop table #a

if object_id ('a1') is not null
drop table a1

create table a1 (id int identity(1,1),a varchar(100))
insert into a1 values('a')
insert into a1 values('b')
insert into a1 values('c')
insert into a1 values('d')

declare @t varchar(100)

set @t = 'a1'
exec( 'select id,a into #a from '+@t)

and here I need to use the temp table out of that scope

select * from #a

or at least like this .

exec('select * from #a ' )

Karthik

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-09 : 08:37:08

Use a global temp table.Use ##a.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-09 : 08:39:20

quote:
Originally posted by ayamas

Use a global temp table.Use ##a.

When I use global temp table in a multiple user environment.
some times when two users the query at the same time record appends that should not occur.
So I prefer in temp table.

Karthik

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-09 : 08:39:33

Only within the same scope

declare @t varchar(100)

set @t = 'a1'
exec( 'select id,a into #a from '+@t+
'GO
select * from #a')

Madhivanan

Failing to plan is Planning to fail

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-09 : 08:44:49

quote:
Originally posted by madhivanan

Only within the same scope

declare @t varchar(100)

set @t = 'a1'
exec( 'select id,a into #a from '+@t+
'GO
select * from #a')

Madhivanan

Failing to plan is Planning to fail

With in the scope we can use,,, But I need it out of that scope. Is ther any alternative, Due to frequent use of many users I cant create it as a static table or global table(because of record appending problem).

Karthik

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-09 : 08:47:06

Will this work

insert into #a exec('select * from '+@t)

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-09 : 08:49:16

quote:
Originally posted by ayamas

Will this work

insert into #a exec('select * from '+@t)

NO actually the #a table will be created in run time only with the values from a table.

So this doesn't works for me

Karthik

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-09 : 08:58:02

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by ayamas

Will this work

insert into #a exec('select * from '+@t)

NO actually the #a table will be created in run time only with the values from a table.

So this doesn't works for me

Karthik

I guess now it is out my SCOPE.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-09 : 09:00:27

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by ayamas

Will this work

insert into #a exec('select * from '+@t)

NO actually the #a table will be created in run time only with the values from a table.

So this doesn't works for me

Karthik

Create a procedure and put that code. It works as you expected

Madhivanan

Failing to plan is Planning to fail

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-09 : 09:31:02

OR shall I use like this??

will it be a Preferred one to use????

if object_id('tempdb..#a') is not null
drop table #a

if object_id ('a1') is not null
drop table a1

create table a1 (id int identity(1,1),a varchar(100))
insert into a1 values('a')
insert into a1 values('b')
insert into a1 values('c')
insert into a1 values('d')

declare @t varchar(100)
declare @con varchar(1000)
set @t = 'a1'
set @con = (select replace( rand(),'.',1))

exec( 'select id,a into ##a'+@con+' from '+@t)

exec('select * from ##a'+@con )

I am using like this so that for every scope the ##a table will be different.
Karthik

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-04-09 : 13:25:12

What exactly are you trying to accomplish? Why would you need the table "out of scope"? Will a work table with some additional fields work for you (like user, create/update date, etc.)? Your last post says "I am using like this so that for every scope the ##a table will be different", isn't that the definition of a temp table???

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources