merge into 笔记

1 IF EXISTS (SELECT 1 FROM sys.types t
           join sys.schemas s on t.schema_id=s.schema_id
           and t.name='tp_AICMonthlyWarrantData' and s.name='MIR')
   DROP TYPE MIR.tp_AICMonthlyWarrantData
GO

CREATE TYPE MIR.tp_AICMonthlyWarrantData AS TABLE
(
    SecId              CHAR(10) NOT NULL,
    EffectiveDate      DATE     NOT NULL,
    WarrantIssued      INT      NULL,
    WarrantExercised   INT  NULL,
    WarrantsCancelled  INT  NULL,
    WarrantsExpired    INT  NULL,
    Outstanding        BIGINT NULL,
    Note               NVARCHAR(500) NULL
)
GO
GRANT EXECUTE ON TYPE::MIR.tp_AICMonthlyWarrantData TO PUBLIC
GO

 

2  MERGE INTO OperationData.MIR.AICMonthlyWarrantData a
        USING @p_warrantTable b
        ON a.SecId=b.SecId and a.EffectiveDate=b.EffectiveDate
        WHEN MATCHED THEN UPDATE
          SET a.Issued=b.WarrantIssued,
              a.Exercised=b.WarrantExercised,
              a.Repurchased=b.WarrantsCancelled,
              a.Expired=b.WarrantsExpired,
              a.Outstanding=b.Outstanding,
              a.Note=b.Note
        WHEN NOT MATCHED THEN
        INSERT (SecId,EffectiveDate,Issued,Exercised,Repurchased,Expired,Outstanding,Note,LastUpdate)
        VALUES(b.SecId,b.EffectiveDate,b.WarrantIssued,b.WarrantExercised,b.WarrantsCancelled,b.WarrantsExpired,
        b.Outstanding,b.Note,getdate());

        MERGE INTO OperationData.MIR.AICMonthlyCovertibleData a
        USING @p_convertibleTable b
        ON a.SecId=b.SecId and a.EffectiveDate=b.EffectiveDate
        WHEN MATCHED THEN UPDATE
          SET a.Issued=b.ConvertibleIssued,
              a.Repurchased=b.ConvertibleRepurchased,
              a.Converted=b.ConvertibleConverted,
              a.Redeemed=b.ConvertibleRedeemed,
              a.Outstanding=b.Outstanding,
              a.Note=b.Note,
              a.ConvertibleTotalValue=b.ConvertibleConvertibleTotalValue,
              a.ConvertibleFairValue=b.ConvertibleConvertibleFairValue
        WHEN NOT MATCHED THEN
        INSERT (SecId,EffectiveDate,Issued,Repurchased,Converted,Redeemed,Outstanding,Note,
        ConvertibleTotalValue,ConvertibleFairValue,LastUpdate)
        VALUES(b.SecId,b.EffectiveDate,b.ConvertibleIssued,b.ConvertibleRepurchased,b.ConvertibleConverted,b.ConvertibleRedeemed,
        b.Outstanding,b.Note,b.ConvertibleConvertibleTotalValue,b.ConvertibleConvertibleFairValue,getdate());