1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| CREATE TABLE update_telephone
(
pk <memeTypeQueLaTableSource> NOT NULL
)
INSERT INTO dbo.update_telephone
SELECT pk
FROM LaTable
WHERE LeTelephone LIKE '%[^0-9]%'
-- Crée une contrainte de clé primaire
-- laquelle ajoute un index cluster qui va accélérer l'ORDER BY du batch qui suit
ALTER TABLE dbo.update_telephone
ADD CONSTRAINT PK_update_telephone PRIMARY KEY (pk)
-----------------------------------------------------------------------------------
DECLARE @i int = 0
, @now datetime
WHILE EXISTS
(
SELECT *
FROM update_telephone
)
BEGIN
SELECT @i += 1
, @now = GETDATE()
BEGIN TRY
BEGIN TRANSACTION
;WITH
CTE AS
(
SELECT TOP 5000 pk
FROM dbo.update_telephone
ORDER BY pk
)
UPDATE LaTable
SET LeTelephone = REPLACE(LeTelephone, ' ', '')
FROM LaTable AS T
INNER JOIN CTE AS C
ON T.pk = C.pk
;WITH
CTE AS
(
SELECT TOP 5000 pk
FROM dbo.update_telephone
ORDER BY pk
)
DELETE FROM dbo.update_telephone
FROM dbo.update_telephone AS UT
INNER JOIN CTE AS C
ON UT.pk = C.pk
COMMIT TRANSACTION
PRINT 'Iteration ' + CAST(@i AS varchar(10)) + ' | Durée : ' + DATEDIFF(second, @now, GETDATE())
END TRY
BEGIN CATCH
DECLARE @err_msg varchar(2048) = ERROR_MESSAGE()
, @err_svt int = ERROR_SEVERITY()
, @err_stt int = ERROR_STATE()
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
RAISERROR(err_msg, @err_svt, @err_stt)
END CATCH
END |
Partager