how can insert sql server stored procedure data to the new table?

Question

I have following sql server stored procedure

SELECT
APO_Order_Id as Purchase_Order_Number,
CompanyID as Company_ID,
p.PJM_UserDefinedProjID as Project_ID,
case m.APO_Use_Alt_Address
end as Project_Site_Address, p.PJM_StartDate as Site_StartDate,
s.CNT_ClientName as Creditor,s.CNT_ABN as Creditor_ABN,s.CNT_UID as Creditor_Id,s.CNT_CreditorType as Creditor_Type, m.APO_Description as Purchase_Order_Description, isnull(d.Order_Amount,0) as Order_Total
FROM
Account_APOrderMaster m
left join
(
SELECT
APOD_Master_Id, APOD_Project_Id, sum(APOD_Total_Amount) as Order_Amount
FROM
Account_APOrderDetail
group by
APOD_Master_Id, APOD_Project_Id
) d on m.Ref_Code = d.APOD_Master_Id
left join Client_Name s on m.APO_Supplier_ID = s.Ref_Code
left join Project_Master p on d.APOD_Project_Id = p.ref_code
left join Client_Name pm on m.APO_ProjectManagerId = pm.Ref_Code
WHERE
apo_order_id = 00122975;

now I need insert data values of CompanyId to the table 'InvoiceMaster'. how can I insert CompanyID to the table?

score 0 · Answer 1 · edited Oct 13 '19 at 16:27

0

If you have access to alter the stored procedure you can write insert command top of your query at procedure

INSERT INTO InvoiceMaster(Name of your column)
    SELECT
        CompanyID as Company_ID
    FROM
        Account_APOrderMaster m
    LEFT JOIN
        (SELECT
             APOD_Master_Id, APOD_Project_Id, 
             SUM(APOD_Total_Amount) AS Order_Amount
         FROM
             Account_APOrderDetail
         GROUP BY
             APOD_Master_Id, APOD_Project_Id) d ON m.Ref_Code = d.APOD_Master_Id
     LEFT JOIN 
         Client_Name s ON m.APO_Supplier_ID = s.Ref_Code
     LEFT JOIN
         Project_Master p ON d.APOD_Project_Id = p.ref_code
     LEFT JOIN
         Client_Name pm ON m.APO_ProjectManagerId = pm.Ref_Code
     WHERE
         apo_order_id = 00122975;

If you can't access the stored procedure code, it's better to write function for solve problems.

If you can't do that you should use ad-hoc distributed queries option in SQL Server insert output of stored procedure in temp table

Or this link for solve your problems

insert stored procedure output to temp table

edited Oct 13 '19 at 16:27

marc_s

732,580
175
1,330
1,459

answered Oct 07 '19 at 10:52

Mahdi Rahimi

564
4
22

then how can I pass values here? – hoyur Oct 07 '19 at 10:54
apo_order_id You mean? @hoyur – Mahdi Rahimi Oct 07 '19 at 10:57
it is order id of order table? – hoyur Oct 07 '19 at 11:00
@hoyur you want to pass parameter? you say: I have following sql server stored procedure i want to know witch parameter? apo_order_id ?? or i was mistake about your question??? – Mahdi Rahimi Oct 07 '19 at 11:05
I need pass CompanyID as variable parameter witch generated in stored procedure – hoyur Oct 07 '19 at 11:10
where is your store procedure? you put on select statement for your question. you have a store procedure that return company id as output and you want to insert this output to table ?? its your question? @hoyur – Mahdi Rahimi Oct 07 '19 at 11:20
yes that is correct I need insert companyID values witch generating using stored procedure to invoiceMaster table – hoyur Oct 07 '19 at 11:25
according to your answer how can I pass values to the table? – hoyur Oct 07 '19 at 11:27
do you access to store procedure code? if you have you can write insert statement on store procedure like my answer. if you dont have access store procedure code can you write function instead of procedure or not? @hoyur – Mahdi Rahimi Oct 07 '19 at 11:53

how can insert sql server stored procedure data to the new table?

1 Answers1