use pivot table in stored procedure

Question

I have a pivot query that works great!

;with PivotData As (
Select KeyField, ColumnName, FieldValue FROM
(SELECT SomeField as KeyField, NameField As ColumnName, DataField as FieldValue
FROM Table1
UNION
SELECT SomeField, NameField, DataField
FROM Table2)
SELECT [List of Fields] FROM PivotData
PIVOT (Max(PivotData.FieldValue) for ColumnName In ([List of Fields])) As P

Now I want to use that query as the source of a temp table in a stored procedure and none of the syntax that I have tried to INSERT the results of the query into a temp table have worked.

Can I create a temporary table from the results? If so, how?

Thanks!

Leslie

What exactly do you need temp tbl for? As an intermediate recordset inside SP or to feed it with SP resultset? Or to fill it inside SP? — OzrenTkalcecKrznaric, Jul 29 '13 at 14:25
I need to run a series of queries on the data returned in order to determine if a record should be inserted in a table in the database. — Leslie, Jul 29 '13 at 17:56
Yes, I understand that. Where is the temp table in that process? — OzrenTkalcecKrznaric, Jul 29 '13 at 20:51
When I start the stored procedure I need to run my pivot query and then run 15 different queries and set a boolean if any conditions are met. At the end of the stored procedure if the boolean is false I don't insert a record. — Leslie, Jul 30 '13 at 11:42
Leslie, I wanted to know what do you fill into temp table. I will assume that you fill result of your pivot query. So, you have to (1) create #temp table, (2) fill #temp with pivot data, (3-1) run query1 and update #temp.myFlag according to that, (3-2) run query2 and update #temp.myFlag according to that, ... (3-15) run query15 and update #temp.myFlag according to that; (4) insert into destinationTbl (...) select ... from #temp where myFlag = 1; HTH — OzrenTkalcecKrznaric, Jul 30 '13 at 12:07
No I am doing just what Sonam posted: SELECT [LIST OF FIELDS] INTO #TEMP FROM PivotData which runs my query and creates the temp table. It's all good. — Leslie, Jul 30 '13 at 12:11

score 0 · Accepted Answer · answered Jul 29 '13 at 14:12

0

Please try this :

;with PivotData As (
Select KeyField, ColumnName, FieldValue FROM
(SELECT SomeField as KeyField, NameField As ColumnName, DataField as FieldValue
FROM Table1
UNION
SELECT SomeField, NameField, DataField
FROM Table2)
SELECT [List of Fields] into #temp FROM PivotData
PIVOT (Max(PivotData.FieldValue) for ColumnName In ([List of Fields])) As P

here you dont have to declare a table or table variable specifically for the purpose.

answered Jul 29 '13 at 14:12

Sonam

3,406
1
12
24

1

Don't forget to delete the temp table at the end of the stored procedure! In addition, the syntax for this is incorrect. – JoeFletch Jul 29 '13 at 14:15
@JoeFletch: what temporary table? Btw temp tbls get shredded when SP exits. – OzrenTkalcecKrznaric Jul 29 '13 at 14:22
I figured it out about 5 minutes after I posted! Isn't that the way it usually works! – Leslie Jul 29 '13 at 14:24
@OzrenTkalčecKrznarić, `#temp`, no? Otherwise the next time that you run the Stored Procedure, the `SELECT ... INTO #temp` will fail. – JoeFletch Jul 29 '13 at 14:30
@JoeFletch: see [this](http://stackoverflow.com/questions/4670924/any-benefit-to-explicitly-dropping-local-temporary-tables-at-the-end-of-a-stored) – OzrenTkalcecKrznaric Jul 29 '13 at 14:40
@OzrenTkalčecKrznarić, thanks for sharing that link. Now I have more research to do on the topic! – JoeFletch Jul 29 '13 at 15:19

use pivot table in stored procedure

1 Answers1