February 18, 2011 at 10:57 am
Is there a easy way to shuffle data in a table? I am trying to scramble a table containing names before i restore this in a test environment. i am talking of millions of records on a sql 2005 instance.
February 18, 2011 at 2:51 pm
How scrambled do you want the names to be ? You could restore to test, then set all the last names to the same thing, or build a temporary table containing fake names, and loop through that assigning them in rotation over the live data. Plenty more options depending on your requirements.
February 18, 2011 at 3:04 pm
I've got a nice example where a replace all the Last Names of a table with one of the standard HTML colors;
I use variations of this to munge data so it's appropriate for demos by replacing real data.
the main requirement is the table must have a primary key (identity typically)
--blank out other data
UPDATE cmcontact
set busemail = 'notreal@email.com',
editedby = 'Administrator',
busfax = '9545555555',
busphone = '8881112222',
buszipcode = '40000',
title = ''
--random first and last names, based on colors and 6 first names:
;With MyColorCTE AS
( SELECT 'AliceBlue' AS TheColor UNION ALL
SELECT 'AntiqueWhite' UNION ALL SELECT 'Aqua' UNION ALL SELECT 'Aquamarine' UNION ALL
SELECT 'Azure' UNION ALL SELECT 'Beige' UNION ALL SELECT 'Bisque' UNION ALL SELECT 'Black' UNION ALL
SELECT 'BlanchedAlmond' UNION ALL SELECT 'Blue' UNION ALL SELECT 'BlueViolet' UNION ALL SELECT 'Brown' UNION ALL
SELECT 'BurlyWood' UNION ALL SELECT 'CadetBlue' UNION ALL SELECT 'Chartreuse' UNION ALL SELECT 'Chocolate' UNION ALL
SELECT 'Coral' UNION ALL SELECT 'CornFlowerBlue' UNION ALL SELECT 'Cornsilk' UNION ALL SELECT 'Crimson' UNION ALL
SELECT 'Cyan' UNION ALL SELECT 'DarkBlue' UNION ALL SELECT 'DarkCyan' UNION ALL SELECT 'DarkGoldenrod' UNION ALL
SELECT 'DarkGray' UNION ALL SELECT 'DarkGreen' UNION ALL SELECT 'DarkKhaki' UNION ALL SELECT 'DarkMagenta' UNION ALL
SELECT 'DarkOliveGreen' UNION ALL SELECT 'DarkOrange' UNION ALL SELECT 'DarkOrchid' UNION ALL SELECT 'DarkRed' UNION ALL
SELECT 'DarkSalmon' UNION ALL SELECT 'DarkSeaGreen' UNION ALL SELECT 'DarkSlateBlue' UNION ALL SELECT 'DarkSlateGray' UNION ALL
SELECT 'DarkTurquoise' UNION ALL SELECT 'DarkViolet' UNION ALL SELECT 'DeepPink' UNION ALL SELECT 'DeepSkyBlue' UNION ALL
SELECT 'DimGray' UNION ALL SELECT 'DodgerBlue' UNION ALL SELECT 'FireBrick' UNION ALL SELECT 'FloralWhite' UNION ALL
SELECT 'ForestGreen' UNION ALL SELECT 'Fuchsia' UNION ALL SELECT 'Gainsboro' UNION ALL SELECT 'GhostWhite' UNION ALL
SELECT 'Gold' UNION ALL SELECT 'Goldenrod' UNION ALL SELECT 'Gray' UNION ALL SELECT 'Green' UNION ALL
SELECT 'GreenYellow' UNION ALL SELECT 'Honeydew' UNION ALL SELECT 'HotPink' UNION ALL SELECT 'IndianRed' UNION ALL
SELECT 'Indigo' UNION ALL SELECT 'Ivory' UNION ALL SELECT 'Khaki' UNION ALL SELECT 'Lavender' UNION ALL
SELECT 'LavenderBlush' UNION ALL SELECT 'LawnGreen' UNION ALL SELECT 'LemonChiffon' UNION ALL SELECT 'LightBlue' UNION ALL
SELECT 'LightCoral' UNION ALL SELECT 'LightCyan' UNION ALL SELECT 'LightGray' UNION ALL SELECT 'LightGreen' UNION ALL
SELECT 'LightPink' UNION ALL SELECT 'LightSalmon' UNION ALL SELECT 'LightSeaGreen' UNION ALL SELECT 'LightSkyBlue' UNION ALL
SELECT 'LightSlateGray' UNION ALL SELECT 'LightSteelBlue' UNION ALL SELECT 'LightYellow' UNION ALL SELECT 'Lime' UNION ALL
SELECT 'LimeGreen' UNION ALL SELECT 'Linen' UNION ALL SELECT 'Magenta' UNION ALL SELECT 'MidnightBlue' UNION ALL
SELECT 'Maroon' UNION ALL SELECT 'MediumAquamarine' UNION ALL SELECT 'MediumBlue' UNION ALL SELECT 'MediumVioletRed' UNION ALL
SELECT 'MediumOrchid' UNION ALL SELECT 'MediumTurquoise' UNION ALL SELECT 'MediumPurple' UNION ALL SELECT 'MediumSpringGreen' UNION ALL
SELECT 'MediumSeaGreen' UNION ALL SELECT 'MediumSlateBlue' UNION ALL SELECT 'MintCream' UNION ALL SELECT 'MistyRose' UNION ALL
SELECT 'Moccasin' UNION ALL SELECT 'NavajoWhite' UNION ALL SELECT 'Navy' UNION ALL SELECT 'OldLace' UNION ALL
SELECT 'Olive' UNION ALL SELECT 'OliveDrab' UNION ALL SELECT 'Orange' UNION ALL SELECT 'OrangeRed' UNION ALL
SELECT 'Orchid' UNION ALL SELECT 'PaleGoldenrod' UNION ALL SELECT 'PaleGreen' UNION ALL SELECT 'PaleTurquoise' UNION ALL
SELECT 'PaleVioletRed' UNION ALL SELECT 'PapayaWhip' UNION ALL SELECT 'PeachPuff' UNION ALL SELECT 'Peru' UNION ALL
SELECT 'Pink' UNION ALL SELECT 'Plum' UNION ALL SELECT 'PowderBlue' UNION ALL SELECT 'Purple' UNION ALL
SELECT 'Red' UNION ALL SELECT 'RosyBrown' UNION ALL SELECT 'RoyalBlue' UNION ALL SELECT 'SaddleBrown' UNION ALL
SELECT 'Salmon' UNION ALL SELECT 'SandyBrown' UNION ALL SELECT 'SeaGreen' UNION ALL SELECT 'Seashell' UNION ALL
SELECT 'Sienna' UNION ALL SELECT 'Silver' UNION ALL SELECT 'SkyBlue' UNION ALL SELECT 'SlateBlue' UNION ALL
SELECT 'SlateGray' UNION ALL SELECT 'Snow' UNION ALL SELECT 'SpringGreen' UNION ALL SELECT 'SteelBlue' UNION ALL
SELECT 'Tan' UNION ALL SELECT 'Teal' UNION ALL SELECT 'Thistle' UNION ALL SELECT 'Tomato' UNION ALL
SELECT 'Turquoise' UNION ALL SELECT 'Violet' UNION ALL SELECT 'Wheat' UNION ALL SELECT 'White' UNION ALL
SELECT 'WhiteSmoke' UNION ALL SELECT 'Yellow' UNION ALL SELECT 'YellowGreen' )
,CTERANDOMLYNUMBER AS
(
SELECT CONTACTTBLKEY,
TheColor,
ROWNUM = ROW_NUMBER() OVER (PARTITION BY CONTACTTBLKEY ORDER BY NEWID())
FROM CMCONTACT --THE TABLE WITH THE KEY
CROSS JOIN MyColorCTE --THE TABLE WITH THE RANDOM VALUE
)
UPDATE CMCONTACT --THE JOINING TABLE
SET CMCONTACT.LASTNAME = CTERANDOMLYNUMBER.TheColor --THE RANDOM VALUE
FROM CTERANDOMLYNUMBER
WHERE CMCONTACT.CONTACTTBLKEY=CTERANDOMLYNUMBER.CONTACTTBLKEY --FOR EACH KEY
AND ROWNUM = 1 --LIMITS TO ONE ARBITRARY VALUE DUE TO THE ORDER BY NEWID
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply