Need a script to mess up the data in a table

Question

Need a script to mess up the data in a table

M G-302788

Valued Member

Points: 51
More actions
February 16, 2006 at 6:04 am

#93385

Hi,
Sorry if someone already asked for this, didn´t find it:
Here is what I need
I have some tables with different columns
A B C D
--------------------------------------------------
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
I need a script to run against that table to get something like:
A B C D
--------------------------------------------------
A4 B1 C4 D2
A3 B2 C2 D1
A2 B3 C1 D4
A1 B4 C3 D3
or
A B C D
--------------------------------------------------
A2 B4 C1 D3
A3 B1 C3 D2
A1 B2 C2 D4
A4 B3 C4 D1
So the idea is to get the table with the same data but with some columns (depends on the table) completely messed up
For example mess up column B and D or B,C and D or just C
Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

I think your example data will not allow this easily, however you probably do not have an actual row with A1, B1, C1, D1, right?

Here is one trick that I use to scramble data for demo databases: create a pair of cursors the first ORDER BY <x> ASC, the 2nd ORDER BY <x> DESC. Now define a pair of nested loops fetching from each cursor, the 1st loop gets the first value from the table, the 2nd loop gets the last value, then insert into your scrambled table.

This really works great for FirstName (from 1st loop) paired with LastName (from 2nd loop), so 1st row of Able, Yellow and last row of Zeb, Acme are the demo data results.

I added a test for both cursors being on the same row and skipped inserting in this situation, to eliminate the possibility of producing an "actual" name.

This really works best when you are producing a set that is smaller that the source.

So using this on your 4 column example would require 3 or 4 nested loops, depending on the actual data mix.

I have also seen examples of this using the NEWID() function to fetch random rows from a table, however I could never get this to function to my satisfaction, as it ended up with too many "actual" names.

Andy

M G-302788 Valued Member Points: 51 More actions · Answer 2

Many thanks Andy,

That´s actually what I need, of course my data is not A1,B1,C1,... it´s a table with names, surnames and bank account numbers...

Cursors will be an option, however I do not know if it will be optimized when running against 50000 rows (on average) :-s

Also, I forgot to mention that I may need to change only one or two columns for each table (I have a few tables)

Thanks anyway

VIDYA-348189 Valued Member Points: 74 More actions · Answer 3

I need the example script for this....

please any one helps me in this regard..

Simharaju.

M G-302788 Valued Member Points: 51 More actions · Answer 4

Hi,

here is the SP I created to scramble two columns, it is easy to change it to one, three or more columns.

It might not be optimized though :-s

CREATE PROCEDURE Scramble2Column(

@Table_Name varchar(50),

@Column1_Name varchar(50),

@Column2_Name varchar(50)

)

AS

if exists (select * from dbo.sysobjects where id = object_id(N'[ROCTempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE ROCTempTable

if exists (select * from dbo.sysobjects where id = object_id(N'[ROCTempTable2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE ROCTempTable2

DECLARE @Query varchar(250)

DECLARE @Column1_Value varchar(50)

DECLARE @Column2_Value varchar(50)

DECLARE @NewColumn1_Value varchar(50)

DECLARE @NewColumn2_Value varchar(50)

--Create a temporary table with a copy of the column

SET @Query = 'SELECT ' + @Column1_Name + ' INTO ROCTempTable FROM ' + @Table_Name + ' WHERE ' + @Column1_Name + ' '''' '

EXEC (@Query)

SET @Query = 'SELECT ' + @Column2_Name + ' INTO ROCTempTable2 FROM ' + @Table_Name + ' WHERE ' + @Column2_Name + ' '''' '

EXEC (@Query)

-- Create one cursor for the table and a second for the ROCTempTable in a new order

SET @Query = 'DECLARE O_Cursor CURSOR FOR Select ' + @Column1_Name + ',' + @Column2_Name + ' FROM ' + @Table_Name

EXEC (@Query)

SET @Query ='DECLARE D_Cursor CURSOR FOR Select ' + @Column1_Name + ' FROM ROCTempTable ORDER BY NewId()'

EXEC (@Query)

SET @Query ='DECLARE D2_Cursor CURSOR FOR Select ' + @Column2_Name + ' FROM ROCTempTable2 ORDER BY NewId()'

EXEC (@Query)

-- Move original table cursor copying data from the dummy cursor

OPEN O_Cursor

OPEN D_Cursor

OPEN D2_Cursor

FETCH NEXT FROM O_Cursor

INTO @Column1_Value,@Column2_Value

FETCH NEXT FROM D_Cursor

INTO @NewColumn1_Value

FETCH NEXT FROM D2_Cursor

INTO @NewColumn2_Value

WHILE @@FETCH_STATUS = 0

BEGIN

if @Column1_Value ''

BEGIN

SET @Query= 'UPDATE ' + @Table_Name+ ' SET ' + @Column1_Name + '='''+ @NewColumn1_Value +''' WHERE CURRENT OF O_Cursor'

EXEC (@QUERY)

FETCH NEXT FROM D_Cursor

INTO @NewColumn1_Value

END

if @Column2_Value ''

BEGIN

SET @Query= 'UPDATE ' + @Table_Name+ ' SET ' + @Column2_Name + '='''+ @NewColumn2_Value +''' WHERE CURRENT OF O_Cursor'

EXEC (@QUERY)

FETCH NEXT FROM D2_Cursor

INTO @NewColumn2_Value

END

FETCH NEXT FROM O_Cursor

INTO @Column1_Value,@Column2_Value

END

CLOSE O_Cursor

DEALLOCATE O_Cursor

CLOSE D_Cursor

DEALLOCATE D_Cursor

CLOSE D2_Cursor

DEALLOCATE D2_Cursor

GO

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 5

>Need a script to mess up the data in a table

Hmm... you could just install Enterprise Manager on developer computers and go to lunch. They'll take care of it for you, and you won't even have to ask.

Eddie Wuerch
MCM: SQL