Solved

Creating a Temp Table from a Stored Procedure

Posted on 2011-02-13
13
764 Views
Last Modified: 2012-05-11
I have a procedure (A) that produces a table and I would like to create a new procedure (B) that creates a temp table that stores all columns structures and row values produced by procedure (A).

How to do so?

IMPORTANT NOTE: I don't want to create my temp table column by column, becasue the table that procedure (A) changes its structure according to some conditions, as well as any table this procedure (A) produces contains so many columns.

I tried this IN PROCEDURE (b) but it failed:

INSERT INTO #MY_TEMP_TABLE EXEC dbo.MY_OLD_STORED_PROC 'value1','value2','value3','value4'

SELECT COUNT(*) FROM #MY_TEMP_TABLE


The error given is: Invalid object name '#MY_TEMP_TABLE'

Please advise
0
Comment
Question by:alfardan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 34885747
I'm not sure if this totally answers your question, but to do what your code is suggesting you would do the following:

: Outer Context (can be a stored proc, query analyser, a connection from an application etc)

create table #Temp / insert into #temp -- construct the temp table as you prefer

: Proc A

insert into #Temp

: Proc B

select from #Temp
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34885796
Create global temp tables.

-- 1st sp
create proc a as
create table ##test (col int)
go

Open in new window

-- 2nd sp
create proc b as 
insert ##test values (1)
select * from ##test
go

Open in new window

-- execute the SPs
exec a
exec b

Open in new window

0
 
LVL 5

Expert Comment

by:karthika_cts
ID: 34886005
Have you tried this command?

SELECT * INTO #MY_TEMP_TABLE EXEC dbo.MY_OLD_STORED_PROC 'value1','value2','value3','value4'
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:alfardan
ID: 34886225
karthika_cts

I just tried it and it gives me this error:

Msg 263, Level 16, State 1, Line 1
Must specify table to select from.
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
0
 

Author Comment

by:alfardan
ID: 34886362
dale_burrell

What I want is the following ni more details:

I have a procedure called (proc_a)

This procedure produces the following table att1
I want now to create a new procedure that uses the table data that was created by proc_a to ultimately produce a differet table.

Please have in the mind the following: the table that proc_a produces contains a count of columns that differ according to the parameters passed to it. Therefore, I can not create a temp table with specific columns matching the table columns produced by proc_a because as I said the structure of this produced table columns count differ according to the parametters passed to proc_a.

Hopefully this illustrates more.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34886694
you can't really do ir without creating the table before hand...

if you want to accept the overhead, and can get the dba to accept the need then you can do it with OPENQUERY

create a linked server pointing to the current server instance

then execute the procedure via an openquery statement using the liked server

e.g.

select c.*
   into #temp
 from OpenQuery(linkedserver,'EXEC PROCEDURE....') as c
0
 

Author Comment

by:alfardan
ID: 34888404
Lowfatspread

What overhead? You mean this "OpenQuery" will or might cause a performance issue or resource overutilization on the server that this DB resides in?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34888469
yes , it will be "slower" than a normal direct execution of the procedure into a table
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34891169
Did you check global temp tables?
0
 

Author Comment

by:alfardan
ID: 34893698
Sharath_123

Won't work for me because I have no authority to alter procedure 1
0
 

Author Comment

by:alfardan
ID: 34893730
Lowfatspread

I tried it as you suggested like this:

select c.* into #temp from OpenQuery(MY_SERVER_NAME,'exec dbo.PROC_A ''val1'',''val2'',''val3'',''val4''') as c

Open in new window


And I got the following error message:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec dbo.PROC_A 'val1','val2','val3','val4'". The OLE DB provider "SQLNCLI" for linked server "MY_SERVER_NAME" indicates that either the object has no columns or the current user does not have permissions on that object.

Open in new window



0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34903793
Execute the SP with 3-part name.

select c.* into #temp from OpenQuery(MY_SERVER_NAME,'exec yourDBName.dbo.PROC_A ''val1'',''val2'',''val3'',''val4''') as c

Open in new window

0
 

Author Closing Comment

by:alfardan
ID: 34935534
Perfect to the point, thank you Sharath_123.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Search query matching words 20 38
SQL Need to calculate age from DOB, no stored procedures or functions allowed 10 57
SQL to JSON 14 39
Please explain Equi-join 3 13
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question