How to get the result of an Exec (@Sql) into a temp table.

Hi all.
I am working on some crosstab logic and needs to get my result into a
temporay table for further use and joins later in the prosedyre. It is
dynamic crosstabs so I don't know the number of columns on beforehand. After
my logic I can get a result that looks nice in QueryAnalyzer usning the Exec
command.
EXEC (@.sql)
What I want i for that result to get into a ad hock created temp table for
further use and joins. Just like
INSERT Col1 INTO #tmpTable FROM Tablename
Looks like the Exec command runs in another "space" so I can't reache the
#tmpTable even if my @.sql is correct with the INTO clause. If I print the
SQL, copies it and runs it it works fine ofcause.
Any ideas
thanx all
geirTry creating your temp table outside the Exec statement.
create table #mytemp
(
a int,
b int
)
Exec('insert ... into #mytemp')
select * from #mytemp
"Geir Holme" <geir@.multicase.no> wrote in message
news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand.
After
> my logic I can get a result that looks nice in QueryAnalyzer usning the
Exec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
> Any ideas
> thanx all
> geir
>|||Hi Jonny.
This works fine as long as you know the number of columns AND the name of
the columns. Since I am inserting a dynamic crosstab i don't know the name
of the columns and the number of columns. That's the big issue here.
Thank you for your interest so far. Mabe you have some more ideas?
regards
geir
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
> Try creating your temp table outside the Exec statement.
> create table #mytemp
> (
> a int,
> b int
> )
> Exec('insert ... into #mytemp')
> select * from #mytemp
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> After
> Exec
>|||Perhaps create a physical table in tempdb and then drop it when not needed.
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
the
the
>|||If you are doing a dynamic query like this, you will have to create a
dynamic temporary table. Consider building a permanent table in tempdb,
using a guid for the table name.
declare @.tableName varchar(40)
set @.tableName = newid()
declare @.query varchar(1000)
set @.query = 'create table tempdb..[' + @.tableName + '] ( column1
varchar(10))'
exec (@.query)
exec ('insert into tempdb..[' + @.tablename + '] values (''hello'') ')
exec ('select * from tempdb..[' + @.tablename + ']')
exec ('drop table tempdb..[' + @.tablename + ']')
Ugly, but it will work. You could also use select into instead of creating
the table. The most important thing is to use a permanent table. The guid
name of the table will ensure no name clashes.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
>|||Geir Holme wrote:
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand. Aft
er
> my logic I can get a result that looks nice in QueryAnalyzer usning the Ex
ec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps, instead of a procedure you'd like to try a function?
use Northwind
go
create FUNCTION udf_getOrders()
returns table
as
return(select top 100 * from orders)
go
select *
into #t
from dbo.udf_getOrders()
go
select * from #t
go
drop table #t
drop function dbo.udf_getOrders
go
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhv1KoechKqOuFEgEQLZcwCgyqhNQjMg+vPO
HFdAUtIi/AFH/5AAoJcq
LOei0tjH80SMhUbTd+uGEPfh
=EhCr
--END PGP SIGNATURE--