Auto Creation and Insertion Of Table Through Executing Procedure

Question

Auto Creation and Insertion Of Table Through Executing Procedure

jchandramouli

SSCommitted

Points: 1772
More actions
January 9, 2009 at 2:54 am

#203769

Hi,
Look into the query below.
CREATE TABLE #MyTable
(UID INT,NAME VARCHAR(100))
INSERT INTO #MyTable SELECT 1,'Name1'
INSERT INTO #MyTable SELECT 2,'Name2'
INSERT INTO #MyTable SELECT 3,'Name3'
SELECT * INTO #TEMP FROM #MyTable
DROP TABLE #TEMP
DROP TABLE #MyTable
Using the above query i can create and insert a table #TEMP in a single statement.
Now check procedure below:
CREATE PROC usp_temp_InsSample
AS
BEGIN
CREATE TABLE #MyTable
(UID INT,NAME VARCHAR(100))
INSERT INTO #MyTable SELECT 1,'Name1'
INSERT INTO #MyTable SELECT 2,'Name2'
INSERT INTO #MyTable SELECT 3,'Name3'
SELECT * FROM #MyTable
DROP TABLE #MyTable
END
I need a query that creates and inserts the records executed by the proc into a table #TEMP.
In Simple Words, When i execute a proc i should get the result in a table as #Temp. This helps me to have a table with the number of columns the proc executes.
Thanks.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I'm not sure I follow you 100% here. Can you give an example of how you want to call the SP and what you expect to be able to do with the returned values?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

If I read you right, you want to use the output of a given stored procedure as input to another table. There are several ways to accomplish that... one way, of course, is to make the temp table in the outer query and then use Insert/Exec to populate the table using the proc as the source of rows...

... but, you have the requirement of...

I need a query that [highlight]creates[/highlight] and inserts the records executed by the proc into a table #TEMP.

Can do. First, let's see what we can do with a built in stored procedure like "sp_who"...

--===== Create and populate the temp table using the result set of a stored procedure

-- as if it were a table.

SELECT *

INTO #Temp

FROM OPENROWSET('SQLOLEDB','Server=server\instance;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

--===== Display the content of the new temp table to show it worked

-- and then drop the temp table for reruns.

SELECT * FROM #Temp

DROP TABLE #Temp

Notice that for this to work, you have to change "server\instance" to the instance name of your server as shown in SMS. Try it.

Now, we've proven that the method works, so why won't this work with your stored procedure (again, change the name of the server\instance AND the name of the database) ?

--===== Create and populate the temp table using the result set of a stored procedure

-- as if it were a table.

SELECT *

INTO #Temp

FROM OPENROWSET('SQLOLEDB','Server=server\instance;Trusted_Connection=Yes;Database=nameofdatabasehere',

'Set FmtOnly OFF; EXEC dbo.usp_temp_InsSample')

--===== Display the content of the new temp table to show it worked.

SELECT * FROM #Temp

DROP TABLE #Temp

Msg 7357, Level 16, State 1, Line 3

Cannot process the object "Set FmtOnly OFF; EXEC dbo.usp_temp_InsSample". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The answer is... the "rowcount" of building the table in the stored proc is treated like an empty result set! You MUST add SET NOCOUNT ON to your example proc so it looks like this...

CREATE PROC usp_temp_InsSample

AS

BEGIN

[font="Arial Black"]SET NOCOUNT ON[/font]

CREATE TABLE #MyTable

(UID INT,NAME VARCHAR(100))

INSERT INTO #MyTable SELECT 1,'Name1'

INSERT INTO #MyTable SELECT 2,'Name2'

INSERT INTO #MyTable SELECT 3,'Name3'

SELECT * FROM #MyTable

DROP TABLE #MyTable

END

... then, the code will work just fine.

There's also a way to make it so the server can be (LOCAL) for everything, but I forget how to do that... I think you have to make a "loop back" linked server called (Local), but I just don't remember. My appologies.

Last but not least... you don't need the "DataBase=" part of the code if you use a 3 part naming convention on the name of the stored procedure to be executed.

Lemme know if you have any questions on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sophie Gravier SSC Enthusiast Points: 150 More actions · Answer 3

Hello Jeff. I realize that you posted this reply over a year ago, but I was having the same problem and went digging through Forums to see what I could find. I always SET NOCOUNT ON in my procedures, so it did not even cross my mind that another coder over on another server that I was linked to did not include that. Such a simple solution - that took me hours to find.. I just had to say Thank You.