Insert into table as many results row returns from subquery along with outer query result

Question

Here is the table definition and this would be destination table of the results

CREATE TABLE [dbo].[CONTRIBUTION_MARGIN_ACCOUNTING] (
    [ID_CONTRIBUTION_MARGIN_ACCOUNTING] [INT] IDENTITY(1000001, 1) NOT NULL
   ,[ID_CUSTOMER_ACCOUNT_POSITION]      [INT]            NOT NULL
   ,[TYP]                               [CHAR](1)        NULL
   ,[CREDIT_TYP]                        [CHAR](1)        NULL
   ,[BILL_DATE]                         [DATETIME]       NULL
   ,[BILL_NO]                           [NVARCHAR](20)   NULL
   ,[POSITION]                          [SMALLINT]       NULL
   ,[CUSTOMER_NO]                       [NVARCHAR](255)  NULL
   ,[CHARGE]                            [NVARCHAR](20)   NULL
   ,[Amount]                            [DECIMAL](18, 6) NULL
   ,CONSTRAINT [PK_CONTRIBUTION_MARGIN_ACCOUNTING] PRIMARY KEY CLUSTERED (
        [ID_CONTRIBUTION_MARGIN_ACCOUNTING] ASC
    ) WITH (PAD_INDEX = OFF
           ,STATISTICS_NORECOMPUTE = OFF
           ,IGNORE_DUP_KEY = OFF
           ,ALLOW_ROW_LOCKS = ON
           ,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CONTRIBUTION_MARGIN_ACCOUNTING] WITH CHECK
    ADD CONSTRAINT [CustomerAccountPosition_CONTRIBUTION_MARGIN_ACCOUNTING]
    FOREIGN KEY([ID_CUSTOMER_ACCOUNT_POSITION])
    REFERENCES [dbo].[CUSTOMER_ACCOUNT_POSITION]([ID_CUSTOMER_ACCOUNT_POSITION])
    ON DELETE CASCADE
GO

Requirement is: As many new records will be inserted into table CONTRIBUTION_MARGIN_ACCOUNTING as many unique combinations ID_CUSTOMER ACCOUNT_POSITION and Amount are found in sub query

Below query gives error, since typically Subquery returns multiple row

SELECT krp.ID_CUSTOMER_ACCOUNT_POSITION
      ,kr.RECEIPT_TYP AS TYP
      ,krp.CALCULATION_TYP AS CREDIT_TYP
      ,kr.BILL_DATE AS BILL_DATE
      ,kr.NO AS BILL_NO
      ,krp.POSITION AS POSITION
      ,kun.NO AS CUSTOMER_NO
      ,(CASE WHEN krp.ID_DELIVERY_POSITION IS NOT NULL
        THEN (SELECT mge.CHARGE_NO AS chr
                    ,SUM(loadingPackage.AMOUNT) AS Amount
              FROM dbo.LOADING_PACKAGE AS loadingPackage
              INNER JOIN dbo.MATERIAL_COMPONENT AS mbe ON loadingPackage.ID_MATERIAL_COMPONENT = mbe.ID_MATERIAL_COMPONENT
              INNER JOIN dbo.MATERIAL_PACK AS mge ON mbe.ID_MATERIAL_COMPONENT = mge.ID_MATERIAL_COMPONENT
              WHERE loadingPackage.ID_DELIVERY_POSITION = DELIVERYPosition.ID_DELIVERY_POSITION
              GROUP BY mge.CHARGE_NO
                      ,loadingPackage.ID_CUSTOMER_ACCOUNT_POSITION
              )
             WHEN krp.ID_DELIVERY_POSITION IS NULL
             THEN (SELECT mge.CHARGE_NO AS ch
                         ,SUM(loadingPackage.AMOUNT) AS Amount
                   FROM dbo.LOADING_PACKAGE AS loadingPackage
                   INNER JOIN dbo.MATERIAL_COMPONENT AS mbe ON loadingPackage.ID_MATERIAL_COMPONENT = mbe.ID_MATERIAL_COMPONENT
                   INNER JOIN dbo.MATERIAL_PACK AS mge ON mbe.ID_MATERIAL_COMPONENT = mge.ID_MATERIAL_COMPONENT
                   WHERE loadingPackage.ID_CUSTOMER_ACCOUNT_POSITION = krp.ID_CUSTOMER_ACCOUNT_POSITION
                   GROUP BY mge.CHARGE_NO
                           ,loadingPackage.ID_CUSTOMER_ACCOUNT_POSITION
             )
            ELSE
                NULL
            END) AS CHARGE
FROM dbo.CUSTOMER ACCOUNT_POSITION AS krp
INNER JOIN dbo.CUSTOMER ACCOUNT AS kr ON kr.ID_CUSTOMER_ACCOUNT = krp.ID_CUSTOMER ACCOUNT
LEFT OUTER JOIN dbo.AMOUNT AS deck ON krp.ID_CUSTOMER_ACCOUNT_POSITION = deck.ID_CUSTOMER_ACCOUNT_POSITION
LEFT OUTER JOIN dbo.DELIVERY_POSITION AS DELIVERYPosition ON krp.ID_DELIVERY_POSITION = DELIVERYPosition.ID_DELIVERY_POSITION
LEFT OUTER JOIN dbo.CUSTOMER AS kun ON kun.ID_CUSTOMER = kr.ID_CUSTOMER
WHERE deck.ID_CUSTOMER_ACCOUNT_POSITION IS NULL
AND kr.STATUS = 'A' ;

Not sure How to achieve this, Example: If some ID_CUSTOMER ACCOUNT_POSITION has 2 Amounts we should create 2 records in table CONTRIBUTION_MARGIN_ACCOUNTING with same ID_CUSTOMER ACCOUNT_POSITION but with different amount.

Sample Data

Ivan Starostin Ivan Starostin · Accepted Answer · 2018-09-07T09:19:23

So put those subqueries to from part, join'em:

SELECT ...
      ,ISNULL(charge_dlvr.CHARGE_NO, charge_pkg.CHARGE_NO) AS CHARGE_NO
      ,ISNULL(charge_dlvr.Amount, charge_pkg.Amount) AS Amount
FROM dbo.CUSTOMER ACCOUNT_POSITION AS krp
...
OUTER APPLY
(SELECT mge.CHARGE_NO
                    ,SUM(loadingPackage.AMOUNT) AS Amount
              FROM dbo.LOADING_PACKAGE AS loadingPackage
              INNER JOIN dbo.MATERIAL_COMPONENT AS mbe ON loadingPackage.ID_MATERIAL_COMPONENT = mbe.ID_MATERIAL_COMPONENT
              INNER JOIN dbo.MATERIAL_PACK AS mge ON mbe.ID_MATERIAL_COMPONENT = mge.ID_MATERIAL_COMPONENT
              WHERE loadingPackage.ID_DELIVERY_POSITION = DELIVERYPosition.ID_DELIVERY_POSITION
              GROUP BY mge.CHARGE_NO
                      ,loadingPackage.ID_CUSTOMER_ACCOUNT_POSITION
) charge_dlvr
OUTER APPLY
(SELECT mge.CHARGE_NO
                         ,SUM(loadingPackage.AMOUNT) AS Amount
                   FROM dbo.LOADING_PACKAGE AS loadingPackage
                   INNER JOIN dbo.MATERIAL_COMPONENT AS mbe ON loadingPackage.ID_MATERIAL_COMPONENT = mbe.ID_MATERIAL_COMPONENT
                   INNER JOIN dbo.MATERIAL_PACK AS mge ON mbe.ID_MATERIAL_COMPONENT = mge.ID_MATERIAL_COMPONENT
                   WHERE loadingPackage.ID_CUSTOMER_ACCOUNT_POSITION = krp.ID_CUSTOMER_ACCOUNT_POSITION
                     AND krp.ID_DELIVERY_POSITION IS NULL
                   GROUP BY mge.CHARGE_NO
                           ,loadingPackage.ID_CUSTOMER_ACCOUNT_POSITION
) charge_pkg
WHERE deck.ID_CUSTOMER_ACCOUNT_POSITION IS NULL

Insert into table as many results row returns from subquery along with outer query result

1 Answers