62 lines
1.5 KiB
PL/PgSQL
62 lines
1.5 KiB
PL/PgSQL
USE [Arizona];
|
|
|
|
IF OBJECT_ID('tempdb..#ADK_KEY_FLA') IS NOT NULL
|
|
BEGIN;
|
|
DROP TABLE #ADK_KEY_FLA;
|
|
END;
|
|
|
|
CREATE TABLE #ADK_KEY_FLA
|
|
(
|
|
ADK_subsidiary INT,
|
|
adk_key VARCHAR(50),
|
|
adk_type INT
|
|
);
|
|
|
|
INSERT INTO #ADK_KEY_FLA
|
|
SELECT ADK_subsidiary,
|
|
ADK_key,
|
|
ADK_type
|
|
FROM [dbo].[Address_key]
|
|
WHERE adk_type = 8
|
|
GROUP BY ADK_subsidiary,
|
|
ADK_key,
|
|
ADK_type
|
|
HAVING COUNT(*) > 1;
|
|
|
|
SELECT COUNT(1) AS cnt,
|
|
'local' AS src
|
|
FROM dbo.Address_key ADK
|
|
INNER JOIN #ADK_KEY_FLA TMP
|
|
ON TMP.ADK_subsidiary = ADK.ADK_subsidiary
|
|
AND TMP.adk_key = ADK.ADK_key
|
|
AND TMP.adk_type = ADK.ADK_type
|
|
WHERE [ADK].[ADK_address] > 1500000000
|
|
UNION
|
|
SELECT COUNT(1) AS cnt,
|
|
'central' AS src
|
|
FROM dbo.Address_key ADK
|
|
INNER JOIN #ADK_KEY_FLA TMP
|
|
ON TMP.ADK_subsidiary = ADK.ADK_subsidiary
|
|
AND TMP.adk_key = ADK.ADK_key
|
|
AND TMP.adk_type = ADK.ADK_type
|
|
WHERE [ADK].[ADK_address] < 1500000000;
|
|
|
|
|
|
BEGIN TRANSACTION;
|
|
SET XACT_ABORT ON;
|
|
SET NOCOUNT ON;
|
|
|
|
DELETE ADK
|
|
FROM dbo.Address_key ADK
|
|
INNER JOIN #ADK_KEY_FLA TMP
|
|
ON TMP.ADK_subsidiary = ADK.ADK_subsidiary
|
|
AND TMP.adk_key = ADK.ADK_key
|
|
AND TMP.adk_type = ADK.ADK_type
|
|
WHERE [ADK].[ADK_address] > 1500000000;
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - adk deleted. '
|
|
+ REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1), ',', ''''), '.00', '')
|
|
+ ' row(s) affected.';
|
|
|
|
ROLLBACK TRANSACTION;
|
|
--COMMIT TRANSACTION
|