?
Solved

Create Stored Procedure which inserts values from multipule queries into table

Posted on 2006-04-14
4
Medium Priority
?
239 Views
Last Modified: 2008-01-09
I'm not as skilled with SQL as i would like to be, I need to insert the values from multipule queries into a new table.  I've done similar inserts with a single query but i'm just not getting this.  

I've included the code i have so far.

-- Drop this procedure if it already exist
IF object_id('SP_Create_AdHoc_Totals_Table') IS NOT NULL
      BEGIN
            DROP PROCEDURE SP_Create_AdHoc_Totals_Table
      END
GO

CREATE PROCEDURE SP_Create_AdHoc_Report_Table @GrantID INT = NULL
AS
      
-- Drop this table if it already exist
IF object_id('tbl_AdHoc_Totals') IS NOT NULL
      BEGIN
            DROP TABLE tbl_AdHoc_Totals
      END
GO

CREATE TABLE tbl_AdHoc_Totals
(
GrantID int,
OriginalTotal decimal NULL,
AdjustmentTotal decimal NULL,
ReimbursedTotal decimal NULL
)

INSERT tbl_AdHoc_Totals
      @GrantID,
      
      (SELECT SUM(lineItems.Total) AS OriginalTotal
      FROM LineItems
      WHERE GrantID = @GrantID),

      (SELECT SUM(Adjustment) AS AdjustmentTotal
      FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid ON lineitems.lineitemid = lopapprove_adjustment_by_lineitemid.lineitemid
      WHERE LineItems.GrantID = @GrantID),

      (SELECT SUM(Reimbursed) AS ReimbursedTotal
      FROM lineitems LEFT JOIN reimbursed_by_lineitemid ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
      WHERE LineItems.GrantID = @GrantID)

SELECT *
FROM tbl_AdHoc_Totals

GO
0
Comment
Question by:R_Hos
2 Comments
 
LVL 4

Accepted Solution

by:
Nazermohideeen earned 1000 total points
ID: 16455944
Modify the Insert statement as

INSERT tbl_AdHoc_Totals
Select
@GrantID,

(SELECT SUM(lineItems.Total) AS OriginalTotal
     FROM LineItems
     WHERE GrantID = @GrantID),

(SELECT SUM(Adjustment) AS AdjustmentTotal
     FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid
     ON lineitems.lineitemid = opapprove_adjustment_by_lineitemid.lineitemid
     WHERE LineItems.GrantID = @GrantID),

(SELECT SUM(Reimbursed) AS ReimbursedTotal
     FROM lineitems LEFT JOIN reimbursed_by_lineitemid
     ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
     WHERE LineItems.GrantID = @GrantID)


NM
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 16456009
IF object_id('SP_Create_AdHoc_Totals_Table') IS NOT NULL
     BEGIN
          DROP PROCEDURE SP_Create_AdHoc_Totals_Table
     END
GO

CREATE PROCEDURE SP_Create_AdHoc_Report_Table @GrantID INT = NULL
AS
     

DECLARE  @tbl_AdHoc_Totals  TABLE(          --- replaced Table with table variables
GrantID int,
OriginalTotal decimal NULL,
AdjustmentTotal decimal NULL,
ReimbursedTotal decimal NULL
)

INSERT INTO @tbl_AdHoc_Totals               -- insert into -Into was missing earlier
SELECT    @GrantID,

    (SELECT SUM(lineItems.Total) FROM LineItems
    WHERE GrantID = @GrantID),

    (SELECT SUM(Adjustment)
    FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid ON lineitems.lineitemid = lopapprove_adjustment_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID),

    (SELECT SUM(Reimbursed) AS ReimbursedTotal
    FROM lineitems LEFT JOIN reimbursed_by_lineitemid ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID)

SELECT *
FROM @tbl_AdHoc_Totals

GO


--If only these much code is present theb you dont need the insert statements





CREATE PROCEDURE SP_Create_AdHoc_Report_Table @GrantID INT = NULL
AS

SELECT    @GrantID AS GrantID,

    (SELECT SUM(lineItems.Total) FROM LineItems
    WHERE GrantID = @GrantID)AS OriginalTotal,

    (SELECT SUM(Adjustment)
    FROM lineitems LEFT JOIN lopapprove_adjustment_by_lineitemid ON lineitems.lineitemid = lopapprove_adjustment_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID)AS AdjustmentTotal,

    (SELECT SUM(Reimbursed) AS ReimbursedTotal
    FROM lineitems LEFT JOIN reimbursed_by_lineitemid ON lineitems.lineitemid = reimbursed_by_lineitemid.lineitemid
    WHERE LineItems.GrantID = @GrantID)ReimbursedTotal

GO
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question