Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auto Creation and Insertion Of Table Through Executing Procedure


Auto Creation and Insertion Of Table Through Executing Procedure

Author
Message
jchandramouli
jchandramouli
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 145
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
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45127 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sophie Gravier
Sophie Gravier
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search