Windows Test Gear - MSSQL.DataMask - Transform.ShuffleRecords Sample SQL

	Data Mask SQL
	Home

Transform.ShuffleRecords

Sample SQL...

/*===================================================================================
Mask Type: Transform.ShuffleRecords =====================================================================================*/
/*---------------------------------------------------------------------
Table: [dbo].[DimCustomer]
Column: [CustomerAlternateKey]
Mask:
* Type: 'Transform: Shuffle Records'
* Category: 'Transform' - Overwrite all rows with an "obsfucated" original value
* Descrip: This mask transforms each row by shuffling it with another randomly
           selected row in the column. All rows are shuffled once.
---------------------------------------------------------------------*/
CREATE TABLE [#ShuffleRecs]
(Id INT IDENTITY(1,1), [CustomerAlternateKey] NVARCHAR(15));

INSERT [#ShuffleRecs]
SELECT [CustomerAlternateKey]
FROM [dbo].[DimCustomer]
ORDER BY NEWID();

UPDATE src
SET src.[CustomerAlternateKey] = shuffled.[CustomerAlternateKey]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY [CustomerAlternateKey] DESC) AS 'RowNumber'
      , *
      FROM [dbo].[DimCustomer]) AS src
INNER JOIN [#ShuffleRecs] AS shuffled
    ON shuffled.Id = src.RowNumber;

-- Cleanup
DROP TABLE [#ShuffleRecs];