USE Diablo
GO
SELECT *
FROM Diablo.Control.vLoadControlDetail
WHERE DataFolder = 'db255_607144'
-- 1718
SELECT *
FROM Diablo.auditcommon.vunvproperty
WHERE DataSupplierId = 217
AND UnvPclId = 33590230
SELECT *
FROM Diablo.audit.vAuditBatch
WHERE Batchid = 8346
SELECT f.FileStatus ,
d.*
FROM Diablo.AuditControl.vLoadControlDetail d
LEFT OUTER JOIN Diablo.control.vLoadControlFileStatus f ON f.FileStatusId = d.FileStatusId
WHERE CntyCd = '06071'
AND f.FileStatusId > 70
ORDER BY AuditBatchId ,
AuditTypeId
USE DiabloSTG
GO
-- 'IX_UNIQ_NC_UnvProperty_PclId_Active'. The duplicate key value is (0145022140000, 1, 218).
SELECT *
FROM DiabloLoad.db255_607144_UniversalProperty
WHERE pcl_id = '0145022140000'
SELECT PREV_PCL_ID ,
*
FROM DiabloLoad.db255_607144_pcl
WHERE pcl_id = '0145022140000'
SELECT *
FROM Diablo.Common.vUnvProperty
WHERE pclid = '0145022140000'
AND datasupplierid = 218
SELECT PCL_ID ,
PCL_SEQ_NBR ,
PREV_PCL_ID
FROM DiabloLoad.db255_607144_pcl
WHERE pcl_id = '0145022140000'
OR PREV_pcl_id = '0145022140000'
SELECT datasupplierid ,
unvpclid ,
cnty_cd ,
pcl_id ,
PCL_SEQ_NBR
FROM ##db255_607144_UniversalProperty
EXCEPT
SELECT datasupplierid ,
unvpclid ,
cnty_cd ,
pcl_id ,
PCL_SEQ_NBR
FROM DiabloLoad.db255_607144_UniversalProperty
SELECT name ,
create_date
FROM sys.tables
WHERE name LIKE 'db255_607144%'
ORDER BY create_date
/*
IF object_id('tempdb..##db255_607144_UniversalProperty') IS NOT NULL DROP TABLE ##db255_607144_UniversalProperty
CREATE TABLE ##db255_607144_UniversalProperty (
[DataSupplierId] smallint NOT NULL,
[UnvPclId] int NOT NULL,
[UpdateId] int NULL,
[UpdateTimestamp] datetime NULL,
[CNTY_CD] char(5) NOT NULL,
[PCL_ID] varchar(45) NULL,
[PCL_SEQ_NBR] tinyint NULL)
go
*/
;
WITH ctprev
AS ( SELECT mt.CNTY_CD ,
mt.PREV_PCL_ID ,
mt.PREV_PCL_SEQ_NBR ,
MAX(mt.PCL_ID) [PCL_ID] ,
MAX(mt.PCL_SEQ_NBR) [PCL_SEQ_NBR]
FROM DiabloLoad.db255_607144_pcl mt
WHERE mt.PREV_PCL_ID IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM DiabloLoad.db255_607144_pcl tst
WHERE tst.CNTY_CD = mt.CNTY_CD
AND tst.PCL_ID = mt.PREV_PCL_ID
AND tst.PCL_SEQ_NBR = mt.PREV_PCL_SEQ_NBR )
GROUP BY mt.CNTY_CD ,
mt.PREV_PCL_ID ,
mt.PREV_PCL_SEQ_NBR
HAVING COUNT(1) = 1
)
INSERT INTO ##db255_607144_UniversalProperty WITH ( TABLOCK )
( DataSupplierId ,
UnvPclId ,
CNTY_CD ,
PCL_ID ,
PCL_SEQ_NBR
)
SELECT ds.DataSupplierId ,
up.UnvPclId ,
mt.CNTY_CD ,
mt.PCL_ID ,
mt.PCL_SEQ_NBR
FROM ctprev mt
INNER JOIN Diablo.tCommon.DataSupplier ds ( NOLOCK ) ON mt.CNTY_CD = ds.CntyCd
INNER JOIN Diablo.tCommon.UnvProperty up ( NOLOCK ) ON up.DataSupplierId = ds.DataSupplierId
AND mt.PREV_PCL_ID = up.PCLID
AND mt.PREV_PCL_SEQ_NBR = up.PCLSEQNBR
WHERE up.Active = 1
SELECT *
FROM ##db255_607144_UniversalProperty
WHERE pcl_id LIKE '0145022140000'
-- Get Universal IDs for Matching parcels
INSERT INTO ##db255_607144_UniversalProperty WITH ( TABLOCK )
( DataSupplierId ,
UnvPclId ,
CNTY_CD ,
PCL_ID ,
PCL_SEQ_NBR
)
SELECT ds.DataSupplierId ,
up.UnvPclId ,
mt.Cnty_CD ,
mt.Pcl_Id ,
mt.Pcl_Seq_Nbr
FROM DiabloLoad.db255_607144_pcl mt
INNER JOIN Diablo.tCommon.DataSupplier ds ( NOLOCK ) ON mt.CNTY_CD = ds.CntyCd
INNER JOIN Diablo.tCommon.UnvProperty up ( NOLOCK ) ON ds.DataSupplierId = up.DataSupplierId
AND mt.PCL_ID = up.PclId
AND mt.PCL_SEQ_NBR = up.PclSeqNbr
AND up.Active = 1
LEFT OUTER JOIN ##db255_607144_UniversalProperty tup ON mt.CNTY_CD = tup.CNTY_CD
AND mt.PCL_ID = tup.PCL_ID
AND mt.PCL_SEQ_NBR = tup.PCL_SEQ_NBR
WHERE tup.UnvPclId IS NULL
--AND mt.pcl_id LIKE '0145022140000'
CREATE UNIQUE CLUSTERED INDEX PCL ON ##db255_607144_UniversalProperty([CNTY_CD], [PCL_ID], [PCL_SEQ_NBR]) WITH (DATA_COMPRESSION = ROW)
SELECT *
FROM ##db255_607144_UniversalProperty
WHERE pcl_id = '0145022140000'
--Create new Universal IDs
BEGIN TRANSACTION
DECLARE @ins TABLE
(
[DataSupplierId] [SMALLINT] ,
[UnvPclId] [INT] ,
[PclId] [VARCHAR](45) ,
[PclSeqNbr] [TINYINT]
)
INSERT INTO Diablo.tCommon.UnvProperty
( DataSupplierId ,
PclId ,
PclSeqNbr
)
--OUTPUT INSERTED.DataSupplierId,
-- INSERTED.UnvPclId,
-- inserted.PclId,
-- inserted.PclSeqNbr
-- INTO @ins (DataSupplierId, UnvPclId, PclId, PclSeqNbr)
SELECT ds.DataSupplierId ,
mt.PCL_ID ,
mt.PCL_SEQ_NBR
FROM DiabloLoad.db255_607144_pcl mt
INNER JOIN Diablo.tCommon.DataSupplier ds ( NOLOCK ) ON mt.CNTY_CD = ds.CntyCd
LEFT OUTER JOIN ##db255_607144_UniversalProperty tup ON mt.CNTY_CD = tup.CNTY_CD
AND mt.PCL_ID = tup.PCL_ID
AND mt.PCL_SEQ_NBR = tup.PCL_SEQ_NBR
WHERE tup.UnvPclId IS NULL
ROLLBACK
/*
BEGIN transaction
DECLARE @ins TABLE (
[DataSupplierId] [SMALLINT],
[UnvPclId] [INT],
[PclId] [VARCHAR](45),
[PclSeqNbr] [TINYINT])
INSERT INTO Diablo.tCommon.UnvProperty
(DataSupplierId,
PclId,
PclSeqNbr)
OUTPUT INSERTED.DataSupplierId,
INSERTED.UnvPclId,
inserted.PclId,
inserted.PclSeqNbr
INTO @ins (DataSupplierId, UnvPclId, PclId, PclSeqNbr)
SELECT ds.DataSupplierId,
mt.PCL_ID,
mt.PCL_SEQ_NBR
FROM DiabloLoad.db255_607144_pcl mt
INNER JOIN Diablo.tCommon.DataSupplier ds (NOLOCK) ON mt.CNTY_CD = ds.CntyCd
LEFT OUTER JOIN DiabloLoad.db255_607144_UniversalProperty tup ON mt.CNTY_CD = tup.CNTY_CD
AND mt.PCL_ID = tup.PCL_ID
AND mt.PCL_SEQ_NBR = tup.PCL_SEQ_NBR
WHERE tup.UnvPclId IS NULL
ROLLBACK
*/
INSERT INTO ##db255_607144_UniversalProperty WITH ( TABLOCK )
( DataSupplierId ,
UnvPclId ,
CNTY_CD ,
PCL_ID ,
PCL_SEQ_NBR
)
SELECT i.DataSupplierId ,
i.UnvPclId ,
ds.CntyCD ,
i.PclId ,
i.PclSeqNbr
FROM @ins i
INNER JOIN Diablo.tCommon.DataSupplier ds ( NOLOCK ) ON i.DataSupplierId = ds.DataSupplierId
--Update users
INSERT INTO [Diablo].[tCommon].[Users]
( [UserName]
)
SELECT DISTINCT
mt.UPDT_ID
FROM DiabloLoad.db255_607144_pcl mt
LEFT OUTER JOIN [Diablo].[tCommon].[Users] u ON u.UserName = mt.UPDT_ID
WHERE u.UserId IS NULL
AND mt.UPDT_ID IS NOT NULL
UPDATE tup
SET UpdateId = u.UserId ,
UpdateTimestamp = mt.UPDT_TMSTP
FROM ##db255_607144_UniversalProperty tup
INNER JOIN DiabloLoad.db255_607144_pcl mt ON mt.CNTY_CD = tup.CNTY_CD
AND mt.PCL_ID = tup.PCL_ID
AND mt.PCL_SEQ_NBR = tup.PCL_SEQ_NBR
LEFT OUTER JOIN Diablo.tCommon.Users u ON u.UserName = mt.UPDT_ID
CREATE UNIQUE NONCLUSTERED INDEX UNVPCL ON ##db255_607144_UniversalProperty([UnvPclId]) WITH (DATA_COMPRESSION = ROW)