Saturday, September 20, 2014
   
Text Size
Login

DataTumble - Randomize Data Rows

What?
This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:

Before:

  1.  ID        Name         DateOfBirth 
  2.  --------- ------------ ----------- 
  3.  1         John Smith   1988-06-24 
  4.  2         Fred Bloggs  1972-11-17 
  5.  3         Another User 1964-02-18 
After:

  1.  ID        Name         DateOfBirth 
  2.  --------- ------------ ----------- 
  3.  1         Fred Bloggs  1964-02-18 
  4.  2         Another User 1988-06-24 
  5.  3         John Smith   1972-11-17 
Looks pretty good, doesn't it? The advantages of this is that you can send this data to your developers and the data types will be correct and maybe they'll resolve issues faster than if they were given scrambled data (see my articles on DataJumble and DataScramble).

How?

  1.  CREATE PROCEDURE [Common].[usp_ScrambleMultivalue] 
  2.  ( 
  3.  @TableName      VARCHAR(MAX), 
  4.  @ColumnName     VARCHAR(MAX), 
  5.  @WhereClause    VARCHAR(MAX) = NULL 
  6.  ) 
  7.  AS 
  8.  /***************************************************************************************************************** 
  9.  ** Author:      -                                                                                 ** 
  10.  ** Created On:  28 August 2009                                                                                  ** 
  11.  ** Revision:    V 0.01                                                                                          ** 
  12.  ** Description: Stored Procedure to scramble Data in a column of a table.                                       ** 
  13.  **              The process will mix the Data using a random function.                                          ** 
  14.  **              Once the Data has been scrambled it won't be possible to get the original set of Data.          ** 
  15.  **                                                                                                              ** 
  16.  ** Usage Examples:                                                                                              ** 
  17.  **              EXEC usp_ScrambleMultivalue 'dbo.TableA', 'ColumnNameA';                                        ** 
  18.  **                                                                                                              ** 
  19.  **              EXEC usp_ScrambleMultivalue 'dbo.TableA', 'ColumnNameA',                                        ** 
  20.  **                      'ColumnB = ''Text'' AND ColumnC = 15000';                                               ** 
  21.  **                                                                                                              ** 
  22.  ** Inputs:                                                                                                      ** 
  23.  **              @TableName   : Name of the table                                                                ** 
  24.  **              @ColumnName  : Name of the column to be scrambled                                               ** 
  25.  **              @WhereClause : Condition to get the set of Data to be scrambled. Can be null                    ** 
  26.  **                                                                                                              ** 
  27.  ** Outputs:                                                                                                     ** 
  28.  **              The Data in @ColumnName column will be scrambled                                                ** 
  29.  **                                                                                                              ** 
  30.  ** Return Values:                                                                                               ** 
  31.  **              None                                                                                            ** 
  32.  **                                                                                                              ** 
  33.  ** Before:                                                                                                      ** 
  34.  **      ID        Name         DateOfBirth                                                                      ** 
  35.  **      --------- ------------ -----------                                                                      ** 
  36.  **      1         John Smith   1988-06-24                                                                       ** 
  37.  **      2         Fred Bloggs  1972-11-17                                                                       ** 
  38.  **      3         Another User 1964-02-18                                                                       ** 
  39.  **                                                                                                              ** 
  40.  ** After:                                                                                                       ** 
  41.  **      ID        Name         DateOfBirth                                                                      ** 
  42.  **      --------- ------------ -----------                                                                      ** 
  43.  **      1         Fred Bloggs  1964-02-18                                                                       ** 
  44.  **      2         Another User 1988-06-24                                                                       ** 
  45.  **      3         John Smith   1972-11-17                                                                       ** 
  46.  **                                                                                                              ** 
  47.  *****************************************************************************************************************/ 
  48.  BEGIN TRY 
  49.   
  50.      BEGIN TRANSACTION  A 
  51.   
  52.      DECLARE @NumberLeft  INT 
  53.      DECLARE @random              INT 
  54.      DECLARE @CurrentID   INT 
  55.      DECLARE @ScrambledID INT 
  56.   
  57.      DECLARE @Data                VARCHAR(MAX) 
  58.      DECLARE @UpdateData  VARCHAR(MAX) 
  59.      DECLARE @Chosen              BIT 
  60.      DECLARE @ChosenID    INT 
  61.      DECLARE @SqlStatement NVARCHAR(MAX) 
  62.      DECLARE @UpdateSqlStatement NVARCHAR(MAX) 
  63.      DECLARE @UpdateCursor   CURSOR 
  64.   
  65.      CREATE TABLE #Scramble 
  66.      ( 
  67.              ID INT identity(1,1), 
  68.              ScrambledID INT, 
  69.              Data VARCHAR(MAX), 
  70.              Chosen      BIT 
  71.      ) 
  72.      -- To dynamically insert the source Data into the temp table 
  73.      IF @WhereClause IS NULL SET @WhereClause = ' 1 = 1' 
  74.   
  75.      SET @SqlStatement = ' 
  76.          INSERT INTO #Scramble 
  77.          SELECT NULL,' + @ColumnName + ',NULL 
  78.          FROM ' + @TableName + 
  79.          ' WHERE ' + @WhereClause 
  80.   
  81.      INSERT INTO #Scramble 
  82.      EXECUTE (@SqlStatement
  83.   
  84.      -- To dynamically create a cursor 
  85.      SET @UpdateSqlStatement = ' 
  86.          SET @UpdateCursor = CURSOR FOR 
  87.          SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @WhereClause + 
  88.          ' FOR UPDATE  OPEN @UpdateCursor' 
  89.   
  90.      EXECUTE sp_executesql @UpdateSqlStatement, N'@UpdateCursor CURSOR OUTPUT'@UpdateCursor OUTPUT 
  91.   
  92.      SELECT @NumberLeft = SUM(1) 
  93.      FROM #Scramble 
  94.   
  95.      FETCH NEXT FROM @UpdateCursor INTO @Data 
  96.   
  97.      WHILE @@FETCH_STATUS = 0 
  98.      BEGIN 
  99.   
  100.              --  OPEN RandomID 
  101.              SET @random = CEILING(RAND()* CONVERT(VARCHAR(MAX),@NumberLeft) ) 
  102.   
  103.              SELECT    @ChosenID = MAX(ID ) 
  104.              FROM  (SELECT TOP (@random) ID FROM #Scramble WHERE Chosen IS NULL ) A 
  105.   
  106.          IF @ChosenID IS NULL RAISERROR('There are no values left to change the Data',16,1) 
  107.   
  108.              UPDATE #Scramble 
  109.              SET Chosen = 1 
  110.              WHERE ID = @ChosenID 
  111.   
  112.          SELECT @UpdateData = Data 
  113.          FROM #Scramble 
  114.          WHERE ID = @ChosenId 
  115.   
  116.          -- To dynamically update the Data in the field being scrambled 
  117.   
  118.          SET @UpdateSqlStatement = ' 
  119.          UPDATE ' + @TableName + ' 
  120.          SET ' + @ColumnName + ' = @UpdateDataIn WHERE CURRENT OF @UpdateCursor
  121.   
  122.          EXECUTE sp_executesql @UpdateSqlStatement, N'@UpdateCursor CURSOR, @UpdateDataIn VARCHAR(MAX)'@UpdateCursor ,@UpdateData 
  123.   
  124.          SELECT @NumberLeft = SUM(1) 
  125.          FROM #Scramble 
  126.          WHERE Chosen IS NULL 
  127.   
  128.              FETCH NEXT FROM @UpdateCursor INTO @Data 
  129.   
  130.      END 
  131.   
  132.      CLOSE @UpdateCursor 
  133.   
  134.      DEALLOCATE @UpdateCursor 
  135.   
  136.      COMMIT TRANSACTION A 
  137.      --print 'committed' 
  138.   
  139.  END TRY 
  140.  BEGIN CATCH 
  141.      ROLLBACK TRANSACTION A 
  142.      --print 'rollback: ' + ERROR_MESSAGE(
  143.      DECLARE @ErrorMessage   VARCHAR(MAX) 
  144.   
  145.      SET @ErrorMessage = ERROR_MESSAGE(
  146.      RAISERROR(@ErrorMessage,16,1) 
  147.  END CATCH 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts