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 2012 Forums
 Transact-SQL (2012)
 insert variable to temporary table

Author  Topic 

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-29 : 02:52:50
hi
i have this query and i want to insert it to temporaray table

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(OrderNo)
from #CTE_Open_Purchase_Orders



FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @query = 'SELECT [ProductKey],' + @cols +'
from
(
select ProductKey,OrderNo,OpenPurchaseOrdersQuantity
from #CTE_Open_Purchase_Orders


) x
pivot
(
max(OpenPurchaseOrdersQuantity)
for OrderNo in (' + @cols + ')
) p '


EXEC (@query)

SELECT * INTO #t FROM @query - it isn't working

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-29 : 03:24:24
I think what you need is this:

CREATE TABLE #t(ProductKey INT, columns VARCHAR(500))

Insert Into #t(ProductKey, Columns)
EXEC (@query)



--------------------
Rock n Roll with SQL
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-29 : 03:54:25
i do not know how many column i will have
Go to Top of Page

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-29 : 07:08:37
You need to use dynamic sql in this case but the temp table will not be accessible outside the dynamic code (if created within the dynamic sql):

EXEC ('SELECT * INTO #T FROM (' + @query + ')TAB;SELECT * FROM #T')

and the above amounts to simply this: EXEC ('SELECT * FROM (' + @query +')TAB')

Both these execs can only do a select from the temp table, so not sure if this serves your purpose:

--------------------
Rock n Roll with SQL
Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-29 : 09:23:40
These are the two ideas to get the result
1. You can use ## temp table that is accessible inside and outside of the dynamic query.
2. You can create #table with single column and can add the remaining columns in loop.

Regards
Viggneshwar A
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-01 : 08:05:02
how can i use simple query to insert to the table?

like
select * into #tt from ?????
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-02-02 : 01:10:19
Make sure to read this fully www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-02-02 : 01:34:48
it is not long article,

i just need to insert the above query to temporary table, or cover it with cte.
but i do not know how to do it.

thanks
Go to Top of Page
   

- Advertisement -