SQLTeam.com | Weblogs | Forums

How to create temp table and load data by running the Stored Procedure

sql2012

#1

have three different SQL scripts, and I am able to execute them and get the desired results. I want to put them all into a Temp Table, and use it as a Stored procedure, and get the data displayed on the temp table. Below is the Procedure that I have created.

I want to know how to create a temp table and insert this data into it by executing the SP.

CREATE PROCEDURE [dbo].[sp_SurveyTracking]

AS
BEGIN
SET NOCOUNT ON;
-- AVAILABLE
SELECT DISTINCT P.Clinic
, P.LastName
, P.FirstName
, SA.SI AS SID
, ser.SD
-- , 'Available' as Status
, ss.SDate
, ss.SDtTm
FROM
Table_Name SA
Inner join Schedule ss on sa.SIID = ss.SIID
Inner join Patient p on p.PID = ss.PID
Inner join Clinical.SSJun ssj on ssj.SSJID = ss.SSJID
Inner join Clinical.SS ser on ser.SSID = ssj.SSID
WHERE
SS.SDtTm IS NOT NULL
AND (SS.SDtTm < SS.SDate)
AND (SS.SDate > GETDATE())
AND SS.Canc = 0
AND sa.AD = 'Completed'
AND GETDATE() > ss.SDate
Order by p.CID;

--COMPLETED
	SELECT DISTINCT p.Clinic
		, p.LastName
		, p.FirstName
		, ser.SD
		--, 'Completed' as Status
		, ss.SDate
	FROM 
		Table_Name SA
	Inner join Schedule ss on sa.SIID = ss.SIID
	Inner join Patient p on p.PID = ss.PID
	Inner join Clinical.SSJun ssj on ssj.SSJID = ss.SSJID
	Inner join Clinical.SS ser on ser.SSID = ssj.SSID
	WHERE
		sa.AD = 'Completed'  and ss.Cancelled = 0 
	order by p.CID;
-- OVERDUE
	SELECT  DISTINCT p.CD
          ,p.LastName
          ,p.FirstName
          ,sa.SIID
          ,ser.SD
          ,'Overdue' as Status
          ,ss.SDate
          ,GetDate() as CurrentDate
	FROM 
		Table_Name SA
	Inner join Schedule ss on sa.SIID = ss.SIID
	Inner join Patient p on p.PID = ss.PID
	Inner join Clinical.SSJun ssj on ssj.SSJID = ss.SSJID
	Inner join Clinical.SS ser on ser.SSID = ssj.SSID
	WHERE
		GetDate() > ss.SDate  and ss.Cancelled = 0 
		order by p.CID;

END

Appreciate any help....


#2

The way you would insert the output of a stored procedure into a table is using the INSERT-EXEC construct. See the documentation here.

INSERT INTO #YourTempTable (col1, col2, col3, ... colN)
EXECUTE [dbo].[sp_SurveyTracking];

But, the insert list should exactly match the output of the stored procedure. Also, if the stored proc returns more than one record set, they all should have the same number and type of columns. In your case you have 3 record sets being returned, but they do not have the same number of columns. Make it so that they all return the same columns even if you have to put some dummy values for some of those.

Another way would be to have a UNION ALL so that only one record set is returned. UNION ALL will force you to have the same number and type of columns for each component in the UNION ALL construct.


#3

I haven't read the whole question so my suggestion might not be relevant, but I've done things like this where I create 3 x #TempTables in the outer SProc which the Inner Sproc then populates - thus the outer Sproc can then access that data.

If the Inner Sproc needs to be able to be called on its own then I would move the "guts" of that inner Sproc to a new Sproc, which uses the 3 x #TempTables approach, and then the original Sproc calls the Child Sproc, and displays the data, but other sprocs can call the Child Sproc and just use the data instead.


#4

Thanks for the reply JamesK.
Kristen, my question was; (let me rephrase it again).
I have 3 sub-sql queries, and each returns different result, OR I can say same result.

  1. I want to make a Stored Procedure of these 3 queries, and
  2. Store the data in a Temp Table.

Each time when I execute the SP, I should be able to see the data in the Temp Table.

Now;

  1. How to create a Temp Table?
  2. Can I create the temp table inside my SP?

#5

My understanding is that you have 3 select statements, and you want to store the results of all the three selects into a single temp table, thus combining all the 3 result sets into one table.

If the result sets are same (i.e, they all have the same number of columns and columns in each result set are of the same or compatible data types) then you can store all of that into a single table. Otherwise you cannot.

Use create statement to create a temp table. Example:

CREATE TABLE #tmp
(
	col1 INT,
	col2 VARCHAR(32),
	col3 foat
);

Once you have created the temp table, use the INSERT-EXEC construct that I posted earlier to store the data into that temp table.

What is the purpose of storing it into a temp table? The scope of the temp table is limited to the session in which it is created.