insert variable to temporary table

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2015-01-29 : 03:54:25

i do not know how many column i will have

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

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

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 ?????

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

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