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:
copyraw
ID        Name         DateOfBirth
--------- ------------ -----------
1         John Smith   1988-06-24
2         Fred Bloggs  1972-11-17
3         Another User 1964-02-18
  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:
copyraw
ID        Name         DateOfBirth
--------- ------------ -----------
1         Fred Bloggs  1964-02-18
2         Another User 1988-06-24
3         John Smith   1972-11-17
  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?
copyraw
CREATE PROCEDURE [Common].[usp_ScrambleMultivalue]
(
@TableName      VARCHAR(MAX),
@ColumnName     VARCHAR(MAX),
@WhereClause    VARCHAR(MAX) = NULL
)
AS
/*****************************************************************************************************************
** Author:      -                                                                                 **
** Created On:  28 August 2009                                                                                  **
** Revision:    V 0.01                                                                                          **
** Description: Stored Procedure to scramble Data in a column of a table.                                       **
**              The process will mix the Data using a random function.                                          **
**              Once the Data has been scrambled it won't be possible to get the original set of Data.          **
**                                                                                                              **
** Usage Examples:                                                                                              **
**              EXEC usp_ScrambleMultivalue 'dbo.TableA', 'ColumnNameA';                                        **
**                                                                                                              **
**              EXEC usp_ScrambleMultivalue 'dbo.TableA', 'ColumnNameA',                                        **
**                      'ColumnB = ''Text'' AND ColumnC = 15000';                                               **
**                                                                                                              **
** Inputs:                                                                                                      **
**              @TableName   : Name of the table                                                                **
**              @ColumnName  : Name of the column to be scrambled                                               **
**              @WhereClause : Condition to get the set of Data to be scrambled. Can be null                    **
**                                                                                                              **
** Outputs:                                                                                                     **
**              The Data in @ColumnName column will be scrambled                                                **
**                                                                                                              **
** Return Values:                                                                                               **
**              None                                                                                            **
**                                                                                                              **
** Before:                                                                                                      **
**      ID        Name         DateOfBirth                                                                      **
**      --------- ------------ -----------                                                                      **
**      1         John Smith   1988-06-24                                                                       **
**      2         Fred Bloggs  1972-11-17                                                                       **
**      3         Another User 1964-02-18                                                                       **
**                                                                                                              **
** After:                                                                                                       **
**      ID        Name         DateOfBirth                                                                      **
**      --------- ------------ -----------                                                                      **
**      1         Fred Bloggs  1964-02-18                                                                       **
**      2         Another User 1988-06-24                                                                       **
**      3         John Smith   1972-11-17                                                                       **
**                                                                                                              **
*****************************************************************************************************************/
BEGIN TRY

    BEGIN TRANSACTION  A

    DECLARE @NumberLeft  INT
    DECLARE @random              INT
    DECLARE @CurrentID   INT
    DECLARE @ScrambledID INT

    DECLARE @Data                VARCHAR(MAX)
    DECLARE @UpdateData  VARCHAR(MAX)
    DECLARE @Chosen              BIT
    DECLARE @ChosenID    INT
    DECLARE @SqlStatement NVARCHAR(MAX)
    DECLARE @UpdateSqlStatement NVARCHAR(MAX)
    DECLARE @UpdateCursor   CURSOR

    CREATE TABLE #Scramble
    (
            ID INT identity(1,1),
            ScrambledID INT,
            Data VARCHAR(MAX),
            Chosen      BIT
    )
    -- To dynamically insert the source Data into the temp table
    IF @WhereClause IS NULL SET @WhereClause = ' 1 = 1'

    SET @SqlStatement = '
        INSERT INTO #Scramble
        SELECT NULL,' + @ColumnName + ',NULL
        FROM ' + @TableName +
        ' WHERE ' + @WhereClause

    INSERT INTO #Scramble
    EXECUTE (@SqlStatement)

    -- To dynamically create a cursor
    SET @UpdateSqlStatement = '
        SET @UpdateCursor = CURSOR FOR
        SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @WhereClause +
        ' FOR UPDATE  OPEN @UpdateCursor'

    EXECUTE sp_executesql @UpdateSqlStatement, N'@UpdateCursor CURSOR OUTPUT', @UpdateCursor OUTPUT

    SELECT @NumberLeft = SUM(1)
    FROM #Scramble

    FETCH NEXT FROM @UpdateCursor INTO @Data

    WHILE @@FETCH_STATUS = 0
    BEGIN

            --  OPEN RandomID
            SET @random = CEILING(RAND()* CONVERT(VARCHAR(MAX),@NumberLeft) )

            SELECT    @ChosenID = MAX(ID )
            FROM  (SELECT TOP (@random) ID FROM #Scramble WHERE Chosen IS NULL ) A

        IF @ChosenID IS NULL RAISERROR('There are no values left to change the Data',16,1)

            UPDATE #Scramble
            SET Chosen = 1
            WHERE ID = @ChosenID

        SELECT @UpdateData = Data
        FROM #Scramble
        WHERE ID = @ChosenId

        -- To dynamically update the Data in the field being scrambled

        SET @UpdateSqlStatement = '
        UPDATE ' + @TableName + '
        SET ' + @ColumnName + ' = @UpdateDataIn WHERE CURRENT OF @UpdateCursor'

        EXECUTE sp_executesql @UpdateSqlStatement, N'@UpdateCursor CURSOR, @UpdateDataIn VARCHAR(MAX)', @UpdateCursor ,@UpdateData

        SELECT @NumberLeft = SUM(1)
        FROM #Scramble
        WHERE Chosen IS NULL

            FETCH NEXT FROM @UpdateCursor INTO @Data

    END

    CLOSE @UpdateCursor

    DEALLOCATE @UpdateCursor

    COMMIT TRANSACTION A
    --print 'committed'

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION A
    --print 'rollback: ' + ERROR_MESSAGE()
    DECLARE @ErrorMessage   VARCHAR(MAX)

    SET @ErrorMessage = ERROR_MESSAGE()
    RAISERROR(@ErrorMessage,16,1)
END CATCH
  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 
Category: Transact-SQL :: Article: 459

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.