sql剪切数据

实际项目当中用到的案例，个人笔记。

USE [CA-SM]
GO
/****** Object:  StoredProcedure [dbo].[PG_SM_AddSum]    Script Date: 07/08/2013 18:56:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--CREATE PROCEDURE [dbo].[pg_AddOtherFee]
ALTER PROCEDURE [dbo].[PG_SM_AddSum]
    @i_SBID int,
    @i_MAID int,
    @sz_CDate varchar(23)
    
AS
    DECLARE
        @iRetVal int,
        @iSSID int,
        @iNSettleDATE int
    SET @iNSettleDATE = CONVERT(varchar(100), GETDATE(), 112)
        
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[#TT]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[#TT]
    
    SELECT MAID, Rname, TotSCnt, TotMFee, SAmt, TotSAmt, BPFee, BName, BAccName, BAccNo, TotWHRAmt, TotWHAmt, TotRAmt, (FeeAmt - BPFee) AS TotAAmt, (MNAmt + TotWHRAmt - TotRAmt - (FeeAmt - BPFee)) AS TotNAmt
    INTO #TT FROM (SELECT MA.MAID, MA.Rname,
    (SELECT COUNT(PKID) FROM PG_SMP_Txn WHERE MAID = MA.MAID AND TxnDay IN (
    SELECT TxnDay FROM PG_SMP_TxnSum TS
    WHERE TS.MAID = MA.MAID AND TS.SettleFlag = 3 AND TS.SettleDay <= @iNSettleDATE)) AS TotSCnt,
    (SELECT SUM(MDA) FROM PG_SMP_Txn WHERE MAID = MA.MAID AND TxnDay IN (
    SELECT TxnDay FROM PG_SMP_TxnSum TS
    WHERE TS.MAID = MA.MAID AND TS.SettleFlag = 3 AND TS.SettleDay <= @iNSettleDATE)) AS TotMFee,
    SUM(TS.SAmt) AS SAmt, SUM(TS.SAmt) AS TotSAmt, SUM(TS.MNAmt) AS MNAmt,
    (SELECT ISNULL(MAX(FeeAmt), '0.00') FROM PG_SM_MerchAdjTxn WHERE MAID = MA.MAID AND FeeID = 1 AND SettleFlag = 3 AND SettleDay <= @iNSettleDATE) BPFee,
    (SELECT BankName FROM [CA-PG]..MM_MerchFinAcc WHERE MAID = MA.MAID) AS BName,
    (SELECT BankAccName FROM [CA-PG]..MM_MerchFinAcc WHERE MAID = MA.MAID) AS BAccName,
    (SELECT BankAccNo FROM [CA-PG]..MM_MerchFinAcc WHERE MAID = MA.MAID) AS BAccNo,
    (SELECT ISNULL(SUM(WS1.WHAmt), '0.00') FROM dbo.PG_SMP_WHTxnSum WS1 WHERE WS1.MAID = MA.MAID AND SettleFlag = 3 AND CONVERT(varchar(100), DATEADD(mm, DATEDIFF(mm,0,CAST(CONVERT(varchar(8),WS1.SettleDay)as datetime))+1, 0), 112) <= CONVERT(int, @iNSettleDATE)) TotWHRAmt,
    (SELECT ISNULL(SUM(WS2.WHAmt), '0.00') FROM dbo.PG_SMP_WHTxnSum WS2 WHERE WS2.MAID = MA.MAID AND SettleFlag IN (2, 3))  - (SELECT ISNULL(SUM(WS1.WHAmt), '0.00') FROM dbo.PG_SMP_WHTxnSum WS1 WHERE WS1.MAID = MA.MAID AND SettleFlag = 3 AND CONVERT(varchar(100), DATEADD(mm, DATEDIFF(mm,0,CAST(CONVERT(varchar(8),SettleDay)as datetime))+1, 0), 112) <= CONVERT(int, @iNSettleDATE)) TotWHAmt,
    (SELECT ISNULL(SUM(RAmt), '0.00') FROM dbo.PG_SMP_RefundTxn WHERE MAID = MA.MAID AND SettleDay <= @iNSettleDATE AND SettleFlag = 3) TotRAmt,
    (SELECT ISNULL(SUM(FeeAmt), '0.00') FROM dbo.PG_SM_MerchAdjTxn WHERE MAID = MA.MAID AND SettleDay <= @iNSettleDATE AND SettleFlag = 3) AS FeeAmt
    
     FROM [CA-PG]..MM_MerchAcc MA
    LEFT JOIN dbo.PG_SMP_TxnSum TS ON MA.MAID = TS.MAID
    LEFT JOIN [CA-PG]..MM_MerchFinAcc MF ON MA.MAID = MF.MAID
    LEFT JOIN [CA-PG]..MM_BillingProf BP ON MF.BProf = BP.BMPID
    WHERE TS.SettleFlag IN (2, 3) AND TS.SettleDay <= CONVERT(int, @iNSettleDATE) AND MA.MAID = @i_MAID
    GROUP BY MA.MAID, MA.Rname, BP.SettleDay, TS.SettleFlag) A
        
    INSERT INTO [dbo].[PG_SM_Sum]
    (SBID, MAID, TotSCnt, TotSAmt, TotMFee, TotWHAmt, TotWHRAmt, TotRAmt, TotAAmt, BPFee, TotNAmt, BName, BAccNo, BAccName, CDate)
    SELECT
    @i_SBID, MAID, TotSCnt, TotSAmt, TotMFee, TotWHAmt, TotWHRAmt, TotRAmt, TotAAmt, BPFee, TotNAmt, BName, BAccNo, BAccName, @sz_CDate FROM #TT
    WHERE MAID = @i_MAID AND (SELECT COUNT(1) FROM [dbo].[PG_SM_Sum] WHERE SBID = @i_SBID AND MAID = #TT.MAID) = 0
    
    SET @iRetVal = 0

CleanUp:
    DROP TABLE #TT
    SELECT @iRetVal RetVal, @iSSID SSID