June 20, 2012 at 7:53 am
I am working on some crazy SQL to do a load from staging tables into the live tables. As most know, this includes inserting data into tables, then saving off the ID created so I can then use it in the linking tables. The only way I was able to find what I needed to do (in terms of loading staging ID values as well as the live ID values) is the MERGE statement.
I am just having some performance issues and if all I am really using it for is to fake an INSERT so I can save off those values, I am sure someone knows of a better way? Below is the SQL I am using
MERGE Policy.PolicyRiskUnit AS Target
USING
( SELECT NULL AS 'PolicyRiskUnitTrID' ,
T1.PolicyEventTrID ,
T1.StagingPolicyRiskUnitTrID ,
T2.RiskUnitTypeMasterTrID ,
T1.PODSPolicyLocationTrID ,
T1.PODSPolicyBuildingTrID ,
T1.PODSPolicyVehicleTrID ,
T2.VehicleLocationID ,
T2.RiskUnitNumber ,
T2.GeographicalRegion ,
T2.FromDate ,
T2.ThruDate ,
T2.TripDescription
FROM #NewPolicyEventRiskUnits T1
INNER JOIN Staging.PolicyRiskUnit T2 ON T1.StagingPolicyRiskUnitTrID = T2.PolicyRiskUnitTrID
) AS Source
ON Source.PolicyRiskUnitTrID = Target.PolicyRiskUnitTrID
WHEN NOT MATCHED BY TARGET
THEN
INSERT (
PolicyEventTrID ,
RiskUnitTypeMasterTrID ,
PolicyLocationTrID ,
PolicyBuildingTrID ,
PolicyVehicleTrID ,
VehicleLocationID ,
PolicyClassCodeTrID ,
RiskUnitNumber ,
GeographicalRegion ,
FromDate ,
ThruDate ,
TripDescription ,
InsertDateTime
)
VALUES
( Source.PolicyEventTrID ,
Source.RiskUnitTypeMasterTrID ,
Source.PODSPolicyLocationTrID ,
Source.PODSPolicyBuildingTrID ,
Source.PODSPolicyVehicleTrID ,
Source.VehicleLocationID ,
Source.PODSPolicyLocationTrID ,
Source.RiskUnitNumber ,
Source.GeographicalRegion ,
Source.FromDate ,
Source.ThruDate ,
Source.TripDescription ,
@CurrentDate
)
OUTPUT
SOURCE.PolicyEventTrID ,
Source.StagingPolicyRiskUnitTrID ,
INSERTED.PolicyRiskUnitTrID
INTO @NewPolicyEventRiskUnits;
Thanks for any help!
June 20, 2012 at 8:32 am
Look up composable DML on this site/google. Something like this should work but would be a major help if we had sample data.
INSERT @NewPolicyEventRiskUnits
SELECT PolicyEventTrID,StagingPolicyRiskUnitTrID,PolicyRiskUnitTrID
FROM( MERGE Policy.PolicyRiskUnit AS Target
USING
( SELECT NULL AS 'PolicyRiskUnitTrID' ,
T1.PolicyEventTrID ,
T1.StagingPolicyRiskUnitTrID ,
T2.RiskUnitTypeMasterTrID ,
T1.PODSPolicyLocationTrID ,
T1.PODSPolicyBuildingTrID ,
T1.PODSPolicyVehicleTrID ,
T2.VehicleLocationID ,
T2.RiskUnitNumber ,
T2.GeographicalRegion ,
T2.FromDate ,
T2.ThruDate ,
T2.TripDescription
FROM #NewPolicyEventRiskUnits T1
INNER JOIN Staging.PolicyRiskUnit T2 ON T1.StagingPolicyRiskUnitTrID = T2.PolicyRiskUnitTrID
) AS Source
ON Source.PolicyRiskUnitTrID = Target.PolicyRiskUnitTrID
WHEN NOT MATCHED BY TARGET
THEN
INSERT (
PolicyEventTrID ,
RiskUnitTypeMasterTrID ,
PolicyLocationTrID ,
PolicyBuildingTrID ,
PolicyVehicleTrID ,
VehicleLocationID ,
PolicyClassCodeTrID ,
RiskUnitNumber ,
GeographicalRegion ,
FromDate ,
ThruDate ,
TripDescription ,
InsertDateTime
)
VALUES
( Source.PolicyEventTrID ,
Source.RiskUnitTypeMasterTrID ,
Source.PODSPolicyLocationTrID ,
Source.PODSPolicyBuildingTrID ,
Source.PODSPolicyVehicleTrID ,
Source.VehicleLocationID ,
Source.PODSPolicyLocationTrID ,
Source.RiskUnitNumber ,
Source.GeographicalRegion ,
Source.FromDate ,
Source.ThruDate ,
Source.TripDescription ,
@CurrentDate
)
OUTPUT
SOURCE.PolicyEventTrID ,
Source.StagingPolicyRiskUnitTrID ,
INSERTED.PolicyRiskUnitTrID
) AS m(PolicyEventTrID,StagingPolicyRiskUnitTrID,PolicyRiskUnitTrID)
June 20, 2012 at 8:53 am
It looks like you could use a Left Anti-Semi Join and bypass the merge completely. Untested code below.
INSERT INTO Policy.PolicyRiskUnit
FROM ( SELECT NULL AS 'PolicyRiskUnitTrID' ,
T1.PolicyEventTrID ,
T1.StagingPolicyRiskUnitTrID ,
T2.RiskUnitTypeMasterTrID ,
T1.PODSPolicyLocationTrID ,
T1.PODSPolicyBuildingTrID ,
T1.PODSPolicyVehicleTrID ,
T2.VehicleLocationID ,
T2.RiskUnitNumber ,
T2.GeographicalRegion ,
T2.FromDate ,
T2.ThruDate ,
T2.TripDescription
FROM #NewPolicyEventRiskUnits T1
INNER JOIN Staging.PolicyRiskUnit T2 ON T1.StagingPolicyRiskUnitTrID = T2.PolicyRiskUnitTrID
) AS SRC
LEFT JOIN Policy.PolicyRiskUnit PPRU ON SRC.PolicyRiskUnitTrID = PPRU.PolicyRiskUnitTrID
WHERE PPRU.PolicyRiskUnitTrID IS NULL
OUTPUT
SOURCE.PolicyEventTrID ,
Source.StagingPolicyRiskUnitTrID ,
INSERTED.PolicyRiskUnitTrID
INTO @NewPolicyEventRiskUnits;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply