Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto Creation and Insertion Of Table Through Executing Procedure Expand / Collapse
Author
Message
Posted Friday, January 9, 2009 2:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 20, 2010 4:47 AM
Points: 42, Visits: 141
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
Post #633198
Posted Friday, January 9, 2009 10:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
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 - by Jeff Moden
Post #633653
Posted Friday, January 9, 2009 12:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,728, Visits: 31,180
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 creates 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
SET NOCOUNT ON

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #633764
Posted Tuesday, March 30, 2010 1:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:31 AM
Points: 6, Visits: 18
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.
Post #893240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse