USE TEMPORARY
GO
IF OBJECT_ID('Temporary.dbo.TransLienPropTest') IS NOT NULL DROP TABLE TEMPORARY.dbo.TransLienPropTest
GO
SELECT TOP 5000000 p.PropId, l.*
INTO TEMPORARY.dbo.TransLienPropTest
FROM Diablo.ttrans.TransLien l INNER JOIN Diablo.Ttax.PROPERTY p ON l.DataSupplierId = p.DataSupplierId AND l.UnvPropId = p.UnvPropId AND l.UnvPropEdition = p.UnvPropEdition
WHERE l.UnvPropId IS NOT NULL
GO
ALTER TABLE TEMPORARY.dbo.TransLienPropTest ADD CONSTRAINT [TransLien_PKC] PRIMARY KEY CLUSTERED ([DataSupplierId], [TransId]) WITH (SORT_IN_TEMPDB = ON)
GO
CREATE NONCLUSTERED INDEX [IX_NC_TransLien_UnvPropId] ON TEMPORARY.dbo.TransLienPropTest ([DataSupplierId], [UnvPropId], [UnvPropEdition]) WITH ( SORT_IN_TEMPDB = ON)
GO
CREATE NONCLUSTERED INDEX [IX_NC_TransLien_UnvPropIdd] ON TEMPORARY.dbo.TransLienPropTest ([DataSupplierId], [PropId]) WITH ( SORT_IN_TEMPDB = ON)
GO
IF OBJECT_ID('Temporary.dbo.PropertyPropTest') IS NOT NULL DROP TABLE TEMPORARY.dbo.PropertyPropTest
GO
SELECT DISTINCT p.* INTO TEMPORARY.dbo.PropertyPropTest
FROM TEMPORARY.dbo.TransLienPropTest l INNER JOIN Diablo.Ttax.PROPERTY p ON l.DataSupplierId = p.DataSupplierId AND l.UnvPropId = p.UnvPropId AND l.UnvPropEdition = p.UnvPropEdition
GO
ALTER TABLE TEMPORARY.dbo.PropertyPropTest ADD CONSTRAINT [Property_PKC] PRIMARY KEY CLUSTERED ([DataSupplierId], [PropId]) WITH (SORT_IN_TEMPDB = ON)
GO
ALTER TABLE TEMPORARY.dbo.PropertyPropTest ADD CONSTRAINT [Property_UNIQ] UNIQUE NONCLUSTERED ([DataSupplierId], [UnvPropId], [UnvPropEdition]) WITH (SORT_IN_TEMPDB = ON)
GO
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
GO
SELECT l.ArmCatCd, p.ApprTotalValAmt
INTO #test
FROM TEMPORARY.dbo.TransLienPropTest l INNER JOIN TEMPORARY.dbo.PropertyPropTest p ON l.DataSupplierId = p.DataSupplierId AND l.UnvPropId = p.UnvPropId AND l.UnvPropEdition = p.UnvPropEdition
WHERE l.DataSupplierId = 358 AND l.TransId BETWEEN 8 AND 8 + 1000000
GO
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
GO
SELECT l.ArmCatCd, p.ApprTotalValAmt
INTO #test
FROM TEMPORARY.dbo.TransLienPropTest l INNER JOIN TEMPORARY.dbo.PropertyPropTest p ON l.DataSupplierId = p.DataSupplierId AND l.PropId = p.PropId
WHERE l.DataSupplierId = 358 AND l.TransId BETWEEN 8 AND 8 + 1000000
GO
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
GO
SELECT l.ArmCatCd, p.ApprTotalValAmt
INTO #test
FROM TEMPORARY.dbo.TransLienPropTest l INNER JOIN TEMPORARY.dbo.PropertyPropTest p ON l.DataSupplierId = p.DataSupplierId AND l.UnvPropId = p.UnvPropId AND l.UnvPropEdition = p.UnvPropEdition
WHERE p.DataSupplierId = 358 AND p.propid BETWEEN 940970 AND 940970 + 100000
GO
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
GO
SELECT l.ArmCatCd, p.ApprTotalValAmt
INTO #test
FROM TEMPORARY.dbo.TransLienPropTest l INNER JOIN TEMPORARY.dbo.PropertyPropTest p ON l.DataSupplierId = p.DataSupplierId AND l.PropId = p.PropId
WHERE p.DataSupplierId = 358 AND p.propid BETWEEN 940970 AND 940970 + 100000
SELECT * FROM TEMPORARY.dbo.PropertyPropTest WHERE DataSupplierId = 358