Convert Access Query to Efficient Stored Proc

[jtrembla]

I need assistance with this stored procedure,

I am trying to add records into the T_PricingDrugGroup table from TableSource if the record doesn't exist in the destination table. I am not doing this on any id, in fact it is a grouping of
PricingDrugID,GeographyID,BrandGeneric that I am concerned with. The source table is essentially unrolled data, where the PricingDrugGroup is the unique grouping at the aforementioned level.
Another caveat is that the versionID must correspond, so the source and pricingdruggroup table will link to the pricingdrugtable to pull back the appriate versioned records.


In Detail...This is what I have done in MS access

1) Create a query to get records from T_PricingDrugGroup by grouptype and version.

SELECT T_PricingDrugGroup.GeographyID, T_PricingDrugGroup.BrandGeneric, T_PricingDrugGroup.PricingDrugID
FROM T_PricingDrugGroup INNER JOIN T_PricingDrug ON T_PricingDrugGroup.PricingDrugID = T_PricingDrug.PricingDrugID
WHERE (((T_PricingDrug.VersionID)=1) AND ((T_PricingDrugGroup.PricingDrugGroupTypeID)=1));

2) next I created an query that selects records from table source by version

SELECT T_TableSource.GeographyID, T_TableSource.PricingDrugID, T_TableSource.BrandGeneric
FROM T_TableSource INNER JOIN T_PricingDrug ON T_TableSource.PricingDrugID = T_PricingDrug.PricingDrugID
WHERE (((T_PricingDrug.VersionID)=1));

3) Next, I created an insert query to insert records into T_PricingDrugGroup that it lacks from TableSource

INSERT INTO T_PricingDrugGroup ( GeographyID, PricingDrugID, BrandGeneric )
SELECT sQ_TableSource.GeographyID, sQ_TableSource.PricingDrugID, sQ_TableSource.BrandGeneric
FROM sQ_TableSource LEFT JOIN sQ_PricingDrugGroup ON (sQ_TableSource.BrandGeneric = sQ_PricingDrugGroup.BrandGeneric) AND (sQ_TableSource.PricingDrugID = sQ_PricingDrugGroup.PricingDrugID) AND (sQ_TableSource.GeographyID = sQ_PricingDrugGroup.GeographyID)
WHERE (((sQ_PricingDrugGroup.GeographyID) Is Null) AND ((sQ_PricingDrugGroup.PricingDrugID) Is Null) AND ((sQ_PricingDrugGroup.BrandGeneric) Is Null));

TableDesigns...


TableSource
==============
PricingDrugID
GeographyID
BrandGeneric
UnitSales
Product


T_PricingDrugGroup
===================
PricingDrugGroupID
PricingDrugID
BrandGeneric
GeographyID
PricingDrugGroupTypeID


T_PricingDrug
===============
PricingDrugID
VersionID



I guess I need assistance to create an efficient stored procedure to replicate the above logic...

 

[jtrembla]

Nevermind, I just found out about the new EXCEPT and INTERSECT command in T-SQl 2005