EXEC(.. insert into #tmp ..); select * from #tmp

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 2000 Forums

SQL Server Development (2000)

EXEC(.. insert into #tmp ..); select * from #tmp

Author

Topic

nfsoft
Starting Member

36 Posts

Posted - 2004-02-26 : 09:45:57

This is my question...
--####
declare @query varchar(2000)
select @query = 'select * from sysobjects' --just for this example
if exists(select * from sysobjects where name like '#migra_tmp1%')
drop table #migra_tmp1
declare @exequery varchar(2000)
select @exequery = STUFF(@query, patindex('%from%', @query) , 0, 'into #migra_tmp1 ')
exec (@exequery)
select * from #migra_tmp1
--####

it can't find #migra_tmp1... what can be done?

Nuno Ferreira

nfsoft
Starting Member

36 Posts

Posted - 2004-02-26 : 10:03:10

SOLVED...
#migra_tmp1 is the problem.

If you use tempdb.dbo.migra_tmp1 the problem is solved.

Tanks any way!

Nuno Ferreira

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-26 : 10:25:34

exec (@exequery)

This is a separate batch and the temp table will be dropped on exit from it.
There is no need to drop the temp table before it.

select @exequery = STUFF(@query, patindex('%from%', @query) , 0, 'into #migra_tmp1 select * from #migra_tmp1')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nfsoft
Starting Member

36 Posts

Posted - 2004-02-26 : 10:59:24

Never the less, since jumps down NESTLEVEL drop all #tables created, the way to pass a result set from a upper nestlevel is by dumping the result in a temp table.
Or else, there is some ather way to do this that I'm not familiar with.

Nuno Ferreira

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-26 : 11:53:40

I believe what you are trying to do is

exec ('select * into #migra_tmp1 from sysobjects')
select * from #migra_tmp1

This will create the temp table in the exec statement and drop it on exit so the select will fail due to the temp table not existing

The temp table created in the exec statement will have a different nest level value to the pre-existing one so there will be no problem creating it. Statements always access the version wioth the highest nest level value.

select * into #migra_tmp1 from sysobjects
exec ('select * into #migra_tmp1 from sysobjects where id < 4 select * from #migra_tmp1')

To populate a temp table from dynamic sql you have to create another connection e.g. using openquery or create the table before the exec

select * into #migra_tmp1 from sysobjects where 1 = 0
insert #migra_tmp1 exec ('select * from sysobjects where id < 4')
select * from #migra_tmp1

select * into #migra_tmp1 from sysobjects where 1 = 0
exec ('insert #migra_tmp1 select * from sysobjects where id < 4')
select * from #migra_tmp1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nfsoft
Starting Member

36 Posts

Posted - 2004-02-26 : 12:26:19

You got it right! Excelent!

select * into #migra_tmp1 from sysobjects where 1 = 0
exec ('insert #migra_tmp1 select * from sysobjects where id < 4')
select * from #migra_tmp1

That is the correct way to do it!

I Tank you... and I am going to use it.
(by the way... this code is part of a migration I am building in TSQL!... hard work)

Nuno Ferreira

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources