USE TEMPORARY
GO
IF OBJECT_ID('Temporary.dbo.TransDeedTest') IS NOT NULL
DROP TABLE TEMPORARY.dbo.TransDeedTest
GO
SELECT TOP 5000000
*
INTO TEMPORARY.dbo.TransDeedTest
FROM Diablo.ttrans.TransDeed
GO
USE TEMPORARY
GO
ALTER TABLE TEMPORARY.dbo.TransDeedTest ADD CONSTRAINT [TransDeed_PKC] PRIMARY KEY CLUSTERED ([DataSupplierId], [TransId]) WITH (SORT_IN_TEMPDB = ON)
GO
IF OBJECT_ID('Temporary.dbo.TransTest') IS NOT NULL
DROP TABLE TEMPORARY.dbo.TransTest
GO
SELECT b.*
INTO TEMPORARY.dbo.TransTest
FROM TEMPORARY.dbo.TransDeedTest a
INNER JOIN Diablo.tTrans.Trans b ON a.DataSupplierId = b.DataSupplierId
AND a.TransId = b.TransId
GO
ALTER TABLE TEMPORARY.dbo.TransTest ADD CONSTRAINT [Trans_PKC] PRIMARY KEY CLUSTERED ([DataSupplierId], [TransId]) WITH (SORT_IN_TEMPDB = ON)
GO
CREATE UNIQUE NONCLUSTERED INDEX [Batch] ON TEMPORARY.dbo.TransTest ([DataSupplierId], [BatchDt], [BatchSeq], [MtgSeq]) WITH ( SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = ON)
GO
IF OBJECT_ID('Temporary.dbo.TransDeedCombinedTest') IS NOT NULL
DROP TABLE TEMPORARY.dbo.TransDeedCombinedTest
GO
SELECT a.DataSupplierId,
b.BatchDt,
b.BatchSeq,
b.MtgSeq,
a.AbsenteeIrisCd,
a.ApnPortionLotInd,
a.ApnResearchInd,
a.AuctionAddr,
a.AuctionAddrZipCd,
a.AuctionCityName,
a.AuctionCszId,
a.AuctionDt,
a.AuctionPostponeCd,
a.AuctionPostponeDt,
a.AuctionStCd,
a.AuctionTime,
a.AuditorFeeNbr,
a.AvmValAmt,
a.BatchId,
a.BeneficiaryPurchasedInd,
a.BypassRejectInd,
a.CaseNbr,
a.CashDownAmt,
a.CertificateNbr,
a.ChronoNbr,
a.CltvPct,
a.CntyMapNbr_1370,
a.CntyRgnId,
a.CntySectionCd,
a.CurrIEQAmt,
a.CurrIEQDt,
a.CurrIEQPct,
a.DeedCatTyp,
a.DefaultAmt,
a.DefaultDt,
a.DistCd,
a.DocFilingDt,
a.DocNbr,
a.DocNbrAlt,
a.DocNbrAlt2,
a.DocTyp,
a.DocYyDt,
a.DqAfnkFlg,
a.EditCounter,
a.EditorId,
a.EditorTimestamp,
a.EditReqdInd,
a.EditTimestamp,
a.EscrowOrderAbbrev,
a.EscrowOrderNbr,
a.ExciseTaxNbr,
a.FinalJudgmntAmt,
a.FirstMissedPaymentDt,
a.ForeClosedLienTransId,
a.GroundRentAmt_1370,
a.HISalePrice,
a.HomeAffordableInd,
a.InputPclMatchKey,
a.InterrelatedInd,
a.LandCourtDocNum,
a.LienAmt,
a.LisPendensTyp,
a.MtgPayoffDeed,
a.MultiPropCnt,
a.MultiSplitCd,
a.MuncCd,
a.NameCleanUpReqdInd,
a.NominalInd,
a.OltvPct,
a.OrigDocDt,
a.OrigDocNbr,
a.OrigIeqAmt,
a.OrigIeqDt,
a.OrigIeqPct,
a.OrigMtgLink,
a.OrigRecordingBook,
a.OrigRecordingDt,
a.OrigRecordingPage,
a.OrigSalePriceAmt,
a.OtherMatchInfo,
a.OtherMatchSeq,
a.OwnrOccupInd,
a.OwnrTransferPct,
a.PartialInterestTransferInd,
a.PclidIrisFrmtd,
a.PclMatchCd,
a.PclMatchInd,
a.PPRInd,
a.PriceCalcInd,
a.PriceSqFtAmt,
a.PriceTypCd,
a.PrimaryCatCd,
a.PrkgLotUnitId_1370,
a.PropStatusCd,
a.PropTypCd,
a.PublishSeq,
a.RealEstateOwnedInd,
a.RealEstateOwnedSaleInd,
a.RecordInd,
a.RecordingBook,
a.RecordingDt,
a.RecordingPage,
a.RefiEqInd,
a.RehabRiderInd,
a.RejectDt,
a.RejectInd,
a.ReRecordedDocInd,
a.ResModelInd,
a.SaleDt,
a.SalePosition,
a.SalePriceAmt,
a.SaleTypCd,
a.SecDCArmsLengthCashInd,
a.SecDCArmsLengthMortgageInd,
a.SecDCInterrelatedInd,
a.SecDCInvestorPurchaseInd,
a.SecDCNewConstructionInd,
a.SecDCRealEstateOwnedInd,
a.SecDCRealEstateOwnedSaleInd,
a.SecDCResaleInd,
a.SecDCResModelInd,
a.SecDCShortSaleInd,
a.SelCarryCd,
a.SevaxAdjImpvPrice,
a.SevaxAdjLandPrice,
a.SevaxAdjSalePrice,
a.SevaxContactName,
a.SevaxCreationInd,
a.SevaxExciseTaxYy,
a.SevaxInsuredAmt,
a.SevaxNominalDeedCd,
a.SevaxPctImpvVal,
a.SevaxPctLandVal,
a.SevaxPctNewMoney,
a.SevaxPctOfSale,
a.SevaxPolicyNbr,
a.SevaxWSPolicyInd,
a.ShortSaleInd,
a.SourceInd,
a.StatisticalExclusion,
a.StndAloneMtgInd,
a.TaxAreaCd,
a.TaxStampCalcInd,
a.TaxStampAmt,
a.TaxStampAmtCd,
a.TimeshareOrderAbbrev,
a.TimeshareOrderNbr,
a.TitleCompanyCd,
a.TitleCompanyNm,
a.TitleOrderAbbrev,
a.TitleOrderAccomodationInd,
a.TitleOrderLegacy,
a.TitleOrderNbr,
a.TransAssdLandAmt,
a.TransAssdTotalAmt,
a.TransTyp,
a.UnvDocTyp,
a.UnvPropEdition,
a.UnvPropId,
a.UnvSaleTypCd,
a.UpdateId,
a.UpdateTimestamp,
a.VendorId,
a.VestingPosition
INTO TEMPORARY.dbo.TransDeedCombinedTest
FROM TEMPORARY.dbo.TransDeedTest a
INNER JOIN TEMPORARY.dbo.TransTest b ON a.[DataSupplierId] = b.[DataSupplierId]
AND a.TransId = b.transid
GO
ALTER TABLE TEMPORARY.dbo.TransDeedCombinedTest ADD CONSTRAINT pk_TransDeedCombinedTest PRIMARY KEY CLUSTERED ([DataSupplierId], [BatchDt], [BatchSeq], [MtgSeq]) WITH ( SORT_IN_TEMPDB = ON)
GO
SELECT COUNT(DISTINCT BatchDt) AS Counter FROM TEMPORARY.dbo.TransDeedCombinedTest
go
SELECT COUNT(DISTINCT b.BatchDt) AS Counter FROM TEMPORARY.dbo.TransDeedTest a INNER JOIN TEMPORARY.dbo.TransTest b ON a.DataSupplierId = b.DataSupplierId AND a.TransId = b.TransId
go