Update data on the fly

Article
09/24/2012

Prendete un datawarehouse abbastanza complesso, con decine di tabelle dei fatti strutturate secondo una modellazione ben precisa:

tutte le tabelle (dei fatti) si chiamano “fact_…”
tutte le chiavi si chiamano “ID…”
(per renderla semplice in questo post) tutti le misure sono definite INT o DECIMAL

Qualcosa come:

Adesso prendete la necessità di voler modificare tutte le tabelle per “offuscare” (=modificare) i dati che il vostro cliente ha inviato per test e collaudo.

La domanda è: posso autogenerare delle istruzioni di UPDATE che risolvano il mio problema (senza impazzire e senza perdere tempo)?

Partiamo da un’idea di come generare valori di test per i due tipi di dato a cui abbiamo limitato lo scenario

 SELECT CAST(RAND(CHECKSUM(NEWID())) * 100000 AS INT)

DECIMAL

 SELECT Round(CAST(1000 + RAND(CHECKSUM(NEWID())) * 150000 AS decimal(8,2)),2)

A questo punto generiamo le istruzioni da eseguire tramite i metadati recuperati con la vista INFORMATION_SCHEMA.COLUMNS:

SELECT

'UPDATE '

+ QUOTENAME( table_schema )

+ '.'

+ QUOTENAME( table_name )

+ ' SET '

+ column_name

+ ' = '

cast

(

case DATA_TYPE when 'int' then (SELECT CAST(RAND(CHECKSUM(NEWID()))

* 100000 AS INT))

when 'decimal' then (SELECT Round(CAST(1000 + RAND(CHECKSUM(NEWID()))

* 150000 AS decimal(8,2)),2))

end

as varchar(30))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name LIKE 'fact%' AND column_name NOT LIKE 'Id%';

Questo il nostro risultato:

A questo punto non ci resta che mandare in esecuzione, una dopo l’altra, le istruzioni appena generate:

 SET NOCOUNT ON;
CREATE TABLE #statements( s nvarchar( max ));


INSERT INTO #statements
SELECT 
    'UPDATE ' 
       + QUOTENAME( table_schema ) 
       + '.' 
       + QUOTENAME( table_name ) 
       + ' SET ' 
       + column_name 
       + ' = ' 
       + 
       cast 
       (
       case DATA_TYPE 
          when 'int' then (SELECT CAST(RAND(CHECKSUM(NEWID()))      
             * 100000 AS INT))
          when 'decimal' then (SELECT Round(CAST(1000 + RAND(CHECKSUM(NEWID())) 
             * 150000 AS decimal(8,2)),2))
       end
       as varchar(30)) 
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name LIKE 'fact%' AND column_name NOT LIKE 'Id%';


DECLARE @s nvarchar( max ) = ( SELECT TOP 1 s
                                 FROM #statements );

WHILE @s IS NOT NULL
    BEGIN
        PRINT @s;
        EXEC sp_executesql @s;

        DELETE FROM #statements
          WHERE s = @s;
        SET @s = ( SELECT TOP 1 s
                     FROM #statements );
    END;

DROP TABLE #statements;
 GO

Update data on the fly

Additional resources