Insert data into two temporary tables using a stored procedure

Question

I have a stored procedure whose data looks like below

Now what I want is, I want to insert its data into two different temp table

First temp table will have these columns

Doc_type, Doc_No, No_of_days

Second temp table will have columns

Username, DocType, No_of_days

I tried like this:

CREATE TABLE #table1 
(
    Doc_type varchar(55),
    Doc_No varchar(55),
    No_of_days varchar(55),
)

INSERT INTO #table1

But it throws an error:

Incorrect syntax near the keyword 'END'.

Here is my complete stored procedure:

Alter procedure GET_INWARD_REMINDER_REPORT 
AS 
BEGIN 
    Select
        U.first_name + ' ' + U.last_name UserName, 
        TH.User_ID, 
        TY.Type_desc Document_Type, 
        RA.mkey Reporting_To,
        U.Email AS UserEmail, 
        RAU.Email AS RA1_Email, 
        RAU.first_name + ' ' + RAU.last_name RAName, 
        TH.Doc_No, 
        DATEDIFF(DAY, TH.LastAction_DateTime, GETDATE()) - DATEDIFF(WK, TH.LastAction_DateTime, GETDATE()) AS No_Of_Days_Doc_Pending 
    from  
        inward_doc_tracking_hdr TH 
    inner join  
        user_mst U ON TH.User_Id = U.mkey 
    inner join 
        emp_mst M ON M.mkey = U.employee_mkey 
    inner join 
        type_mst_a TY ON TY.master_mkey = TH.doc_type 
    inner join 
        emp_mst RA ON RA.mkey = M.Reporting_To 
    inner join  
        user_mst RAU ON RAU.employee_mkey = RA.mkey 
    where 
        TH.Status_flag NOT IN (5,14)  --- 5 for close, 14 for return
        and TH.To_user IS NOT NULL 

CREATE TABLE #table1 
(   
    Doc_type varchar(55),
    Doc_No varchar(55),
    No_of_days varchar(55),
)

INSERT INTO #table1

Scope of temp table is till session ie once sp executed .Temp table will no longer exist ... — sandeep rawat
@sandeeprawat: I know that, but help me with how to insert into the temp table — hud
still you need to insert data then move create table at top . and do insert into \ — sandeep rawat
@sandeeprawat: yes it worked, but how to check its record of temp1 table ? — hud
that is why i was telling it is useless until you are not using the result further .. for testing do select at end — sandeep rawat

NEER NEER · Accepted Answer · 2016-07-19T06:49:37

Get all data to temp table then insert.

INSERT INTO #MainTemp
EXEC [GET_INWARD_REMINDER_REPORT];

INSERT INTO #table1
select Doc_type, Doc_No, No_of_days from #MainTemp

INSERT INTO #table2
select Username, DocType, No_of_days from #MainTemp

UPDATED

You can use MainTempTable.

Alter procedure GET_INWARD_REMINDER_REPORT 
AS 
BEGIN 

IF EXISTS(SELECT * FROM   dbo.MainTempTable) 
  DROP TABLE dbo.MainTempTable

Select 
U.first_name + ' ' + U.last_name UserName, 
TH.User_ID, 
TY.Type_desc Document_Type, 
RA.mkey Reporting_To, 
U.Email AS UserEmail, 
RAU.Email AS RA1_Email, 
RAU.first_name + ' ' + RAU.last_name RAName, 
TH.Doc_No, 
DATEDIFF(DAY,TH.LastAction_DateTime,GETDATE()) - DATEDIFF(WK,TH.LastAction_DateTime, GETDATE()) 
AS No_Of_Days_Doc_Pending 

INTO MainTempTable

from inward_doc_tracking_hdr TH 
inner join  
user_mst U ON TH.User_Id = U.mkey 
inner join 
emp_mst M ON M.mkey = U.employee_mkey 
inner join 
type_mst_a TY ON TY.master_mkey = TH.doc_type 
inner join 
emp_mst RA ON RA.mkey = M.Reporting_To 
inner join  
user_mst RAU ON RAU.employee_mkey = RA.mkey 
where 
TH.Status_flag NOT IN (5,14) --- 5 for close, 14 for return 
and TH.To_user IS NOT NULL 

SELECT 
   userName, 
   COUNT(Doc_No) CountofDocNo
FROM 
   MainTempTable 
GROUP BY 
   userName

END

Insert data into two temporary tables using a stored procedure

1 Answers