January 4, 2012 at 1:05 pm
Hi Guys,
I am trying to create a function to shuffle names in a Customers table. The query below seem to work fine; however, it is taking a long time for 150,000 records.
Declare @id int
DECLARE mycursor CURSOR FOR SELECT id FROM Customers
OPEN mycursor
FETCH NEXT FROM mycursor INTO @id;
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE Customers
SET FirstName = (SELECT TOP 1 FirstName FROM Customers ORDER BY NEWID())
WHERE id = @id
UPDATE Customers
SET LastName = (SELECT TOP 1 LastName FROM Customers ORDER BY NEWID())
WHERE id = @id
FETCH NEXT FROM mycursor INTO @id;
END
CLOSE mycursor;
DEALLOCATE mycursor;
I saw one solution from Matt Miller
http://www.sqlservercentral.com/Forums/Topic427826-338-2.aspx
Need some help... How can I do that shuffle in Update Query as I cannot drop and re-create the Customers table.
Thanks,
Laura
January 4, 2012 at 1:11 pm
Are you just trying to randomize the first names and the last names? Your performance is because of the cursor. You could create a temp table, then insert firstname order by newid(), then do an update from select order by newid(), Then update your original table using the newly shuffled values in your temp table. I have a meeting in about 20 minutes but I will try to roll this up. Should be pretty straight forward.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2012 at 1:16 pm
yes correct randomize the firstnames and lastnames... thanks a lot.
January 4, 2012 at 1:40 pm
something like this should do it in a single statement;
see if this does what you would like:
With FNames AS (
select distinct firstname from Customer),
LNames AS (
select distinct lastname from Customer),
Randomized AS (
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS RANDID,
FNames.firstname,
LNames.lastname
FROM FNames
CROSS JOIN LNames
cross join sys.columns) --just to get LOTS of rows.
UPDATE Customer
SET firstname = Randomized.firstname,
lastname = Randomized.lastname
FROM Randomized
WHERE ID = Randomized.RANDID
Lowell
January 4, 2012 at 1:51 pm
thanks Lowell.
January 4, 2012 at 2:36 pm
Sean do I need to update using join using id? Also I found names list from census site... probably I can use that..
January 5, 2012 at 11:24 am
Lowell I tried the one suggested by you. It seem to take a long time for around 50K records. It ran for 15 minutes and I killed it. Do you think using temp tables and not the CTE will make the query faster?
Thanks,
Laura
January 5, 2012 at 12:18 pm
This one ran much much faster... I need to testing and some addition...
I used lastnames and firstnames I got from census site so I expect the names to be different than original.
declare @fncount int
declare @lncount int
select @fncount=count(*) from dbo.CensusFirstNamesMale
select @lncount=count(*) from dbo.CensusLastNames
select top 150000 --get the max(ID) from the customers table and use that in dynamic query to create this table
rid = identity(int, 1, 1),
cast(rand(checksum(newid()))*(@fncount-1) as int)+1 as fnid,
cast(rand(checksum(newid()))*(@lncount-1) as int)+1 as lnid
into randomfullname
from Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
create index ix_rfn1 on randomFullname(fnid,lnid)
select datediff(ms, @g, getdate())
Update C
Set LastName = ln.LastName, FirstName = fn.FirstName
from randomFullname r
inner join CensusFirstNamesMale fn on r.fnid=fn.fnid
inner join CensusLastNames ln on r.lnid=ln.lnid
join Customers c on r.rid = c.id
January 5, 2012 at 12:32 pm
OK I have a fakenames table that I keep around for various times I need this type of thing. I included the first 100 rows from that as a starting point.
Here is that table:
CREATE TABLE [dbo].[fakenames](
[FakeNameID] [int] IDENTITY(1,1) NOT NULL,
[gender] [varchar](6) NOT NULL,
[FName] [varchar](20) NOT NULL,
[LName] [varchar](20) NOT NULL,
[Address] [varchar](100) NOT NULL,
[City] [varchar](100) NOT NULL,
[ST] [char](2) NOT NULL,
[Zip] [varchar](5) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[fakenames] ON
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (1, N'male', N'Francisco', N'Farley', N'3562 Bastin Drive', N'Philadelphia', N'PA', N'19103')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (2, N'female', N'Ashley', N'Depriest', N'3201 Rosebud Avenue', N'BULL SHOALS', N'AR', N'72619')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (3, N'male', N'Samuel', N'Numbers', N'3861 Collins Street', N'State College', N'PA', N'16801')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (4, N'female', N'Georgia', N'Beckley', N'2601 Hershell Hollow Road', N'EVERETT', N'WA', N'98208')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (5, N'female', N'Bernice', N'Narvaez', N'3352 Stewart Street', N'Indianapolis', N'IN', N'46204')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (6, N'male', N'Thomas', N'Rios', N'445 Horner Street', N'Boardman', N'OH', N'44512')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (7, N'female', N'Janice', N'Williams', N'3514 Gordon Street', N'Los Angeles', N'CA', N'90017')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (8, N'male', N'James', N'Terry', N'468 Lighthouse Drive', N'Monett', N'MO', N'65708')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (9, N'female', N'Martha', N'Hatcher', N'2721 Hawks Nest Lane', N'Saint Louis', N'MO', N'63108')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (10, N'male', N'Marvin', N'Lamon', N'4617 Sussex Court', N'KOSSE', N'TX', N'76653')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (11, N'female', N'Connie', N'Rowell', N'1771 Rocky Road', N'Ambler', N'PA', N'19002')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (12, N'male', N'Shane', N'Allen', N'643 Poplar Lane', N'Ft Lauderdale', N'FL', N'33311')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (13, N'female', N'Deborah', N'Mcalpine', N'3626 Daylene Drive', N'Livonia', N'MI', N'48150')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (14, N'male', N'Rodney', N'Hill', N'3621 Cunningham Court', N'Farmington Hills', N'MI', N'48335')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (15, N'male', N'Christopher', N'Smith', N'314 Saint Clair Street', N'Jackson', N'MS', N'39211')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (16, N'male', N'Rick', N'Clemmer', N'639 Camel Back Road', N'Tulsa', N'OK', N'74116')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (17, N'male', N'Lonnie', N'Timm', N'769 Grant View Drive', N'Milwaukee', N'WI', N'53218')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (18, N'male', N'Ernest', N'Mcgrail', N'3286 Parrill Court', N'Valparaiso', N'IN', N'46383')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (19, N'male', N'Michael', N'Horowitz', N'4900 Birch Street', N'El Paso', N'TX', N'79915')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (20, N'female', N'Anita', N'Melton', N'94 Andy Street', N'HUMBOLDT', N'SD', N'57035')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (21, N'male', N'Kyle', N'Turner', N'1216 Goff Avenue', N'Otsego', N'MI', N'49078')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (22, N'female', N'Brenda', N'Peabody', N'1825 Ingram Street', N'Dayton', N'OH', N'45402')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (23, N'female', N'Jane', N'Jones', N'1439 Hurry Street', N'Roanoke', N'VA', N'24011')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (24, N'male', N'Timothy', N'Makela', N'3484 Concord Street', N'CHARLOTTE', N'NC', N'28134')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (25, N'male', N'Michael', N'Diaz', N'809 Norman Street', N'Los Angeles', N'CA', N'90042')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (26, N'male', N'Enrique', N'King', N'4779 Doe Meadow Drive', N'Washington', N'MD', N'20004')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (27, N'male', N'Thurman', N'Padgett', N'1395 Neuport Lane', N'Norcross', N'GA', N'30071')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (28, N'male', N'Daniel', N'Thomas', N'2011 Golden Ridge Road', N'Troy', N'NY', N'12180')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (29, N'female', N'Edith', N'Jackson', N'503 Nancy Street', N'DURHAM', N'NC', N'27713')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (30, N'female', N'Teresa', N'Stout', N'655 Star Trek Drive', N'Tallahassee', N'FL', N'32301')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (31, N'male', N'Enrique', N'Harrington', N'2319 Fincham Road', N'Los Angeles', N'CA', N'90017')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (32, N'male', N'John', N'Daniels', N'249 Armory Road', N'MAYSVILLE', N'NC', N'28555')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (33, N'male', N'Richard', N'Jennings', N'2797 Derek Drive', N'East Liverpool', N'OH', N'43920')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (34, N'female', N'Carol', N'Caperton', N'714 Hidden Valley Road', N'Harrisburg', N'PA', N'17101')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (35, N'female', N'Marcie', N'Ohlson', N'135 Lamberts Branch Road', N'Orlando', N'FL', N'32801')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (36, N'male', N'George', N'Nebeker', N'3007 Boone Street', N'Corpus Christi', N'TX', N'78411')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (37, N'male', N'Eddie', N'Seaman', N'2408 Sunny Day Drive', N'Santa Ana', N'CA', N'92701')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (38, N'male', N'Don', N'Howard', N'45 Cherry Tree Drive', N'Jacksonville', N'FL', N'32216')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (39, N'male', N'David', N'Maltby', N'1454 Edgewood Road', N'PINE BLUFF', N'AR', N'71601')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (40, N'male', N'Billy', N'Fultz', N'2890 University Street', N'Seattle', N'WA', N'98109')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (41, N'male', N'James', N'Leger', N'313 Howard Street', N'Grand Rapids', N'MI', N'49503')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (42, N'female', N'Kimberly', N'Ringer', N'446 Hillcrest Lane', N'El Toro', N'CA', N'92630')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (43, N'female', N'Leatrice', N'Gragg', N'4051 Pine Garden Lane', N'Atlanta', N'GA', N'30339')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (44, N'male', N'Aaron', N'Weeks', N'1523 Kovar Road', N'Worcester', N'MA', N'01608')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (45, N'female', N'Vera', N'Ibrahim', N'355 Franklin Street', N'Dothan', N'AL', N'36303')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (46, N'male', N'Norman', N'Bordeaux', N'2732 Cedar Street', N'Little Rock', N'AR', N'72212')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (47, N'female', N'Ann', N'Booth', N'2175 Hurry Street', N'Harrisonburg', N'VA', N'22801')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (48, N'male', N'Gerald', N'Shank', N'459 Tetrick Road', N'ARCADIA', N'FL', N'33821')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (49, N'male', N'John', N'Thompson', N'2091 Oral Lake Road', N'Wayzata', N'MN', N'55391')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (50, N'female', N'Linda', N'Galvin', N'4365 Southern Avenue', N'Des Moines', N'IA', N'50309')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (51, N'female', N'Kathryn', N'Servantes', N'3081 Ingram Street', N'West Liberty', N'OH', N'43357')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (52, N'male', N'Ervin', N'Long', N'2136 White Avenue', N'Corpus Christi', N'TX', N'78476')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (53, N'male', N'James', N'Peterson', N'220 August Lane', N'Alexandria', N'LA', N'71302')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (54, N'male', N'Billy', N'Catron', N'4768 Deer Haven Drive', N'Greenville', N'SC', N'29607')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (55, N'male', N'Michael', N'Burris', N'3945 Melody Lane', N'Richmond', N'VA', N'23219')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (56, N'female', N'Debbie', N'Ferguson', N'2180 Eva Pearl Street', N'Baton Rouge', N'LA', N'70810')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (57, N'male', N'Demetrius', N'Sinclair', N'3736 Rhapsody Street', N'Ocala', N'FL', N'34471')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (58, N'female', N'Cora', N'Clark', N'4106 Cottonwood Lane', N'Southfield', N'MI', N'48075')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (59, N'male', N'Kevin', N'Norman', N'3254 Primrose Lane', N'Madison', N'WI', N'53703')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (60, N'male', N'Rene', N'Gould', N'1154 Mcwhorter Road', N'Starkville', N'MS', N'39759')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (61, N'female', N'Guadalupe', N'Mobley', N'1753 Sardis Sta', N'Cleburne', N'TX', N'76031')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (62, N'male', N'Joseph', N'Waddle', N'1433 Traction Street', N'Greenville', N'SC', N'29601')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (63, N'female', N'Elfrieda', N'Miller', N'4605 Rubaiyat Road', N'Grand Rapids', N'MI', N'49503')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (64, N'female', N'Ruby', N'Hasson', N'627 Dane Street', N'The Dalles', N'WA', N'97058')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (65, N'female', N'Alice', N'Mcdonald', N'4156 Hiney Road', N'LAS VEGAS', N'NV', N'89128')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (66, N'male', N'Paul', N'Jenkins', N'2240 Marigold Lane', N'Miami Springs', N'FL', N'33166')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (67, N'male', N'Wendell', N'Hirano', N'1794 Modoc Alley', N'Meridian', N'ID', N'83642')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (68, N'female', N'Ann', N'Sroka', N'581 Bel Meadow Drive', N'Ontario', N'CA', N'91762')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (69, N'female', N'Ronda', N'Loyd', N'2950 Hickory Heights Drive', N'Baltimore', N'MD', N'21202')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (70, N'female', N'Phoebe', N'Mckown', N'1855 Shinn Street', N'New York', N'NY', N'10022')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (71, N'male', N'John', N'Hill', N'842 Meadowbrook Mall Road', N'Culver City', N'CA', N'90232')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (72, N'female', N'Mary', N'Bennett', N'4990 Rinehart Road', N'Miami', N'FL', N'33169')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (73, N'female', N'Eve', N'Robinson', N'2869 Pinnickinnick Street', N'CARTERET', N'NJ', N'07008')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (74, N'male', N'John', N'Lyons', N'1108 Olive Street', N'Toledo', N'OH', N'43602')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (75, N'male', N'Ronald', N'Ries', N'3523 Glenwood Avenue', N'CLEVELAND', N'OH', N'44114')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (76, N'female', N'Ruby', N'Cummins', N'2144 Goldleaf Lane', N'Lyndhurst', N'NJ', N'07071')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (77, N'female', N'Gloria', N'Soto', N'4480 Coulter Lane', N'Richmond', N'VA', N'23224')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (78, N'male', N'Albert', N'Covington', N'4270 Upland Avenue', N'STONY RIDGE', N'OH', N'43463')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (79, N'male', N'Robert', N'Sherwood', N'2751 Brannon Avenue', N'Jacksonville', N'FL', N'32202')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (80, N'male', N'Jesus', N'Clark', N'4424 Overlook Drive', N'Lafayette', N'IN', N'47904')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (81, N'female', N'Kari', N'Brookins', N'379 Grove Street', N'Bohemia', N'NY', N'11716')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (82, N'male', N'Maurice', N'Martin', N'2997 Wilkinson Street', N'Nashville', N'TN', N'37211')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (83, N'female', N'Lisa', N'Suggs', N'708 Derek Drive', N'Akron', N'OH', N'44308')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (84, N'male', N'Theodore', N'Kim', N'3933 August Lane', N'Shreveport', N'LA', N'71101')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (85, N'male', N'Greg', N'Olson', N'4421 Forest Drive', N'Washington', N'VA', N'20036')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (86, N'female', N'Stephanie', N'Parkes', N'3271 Ash Street', N'Dallas', N'TX', N'75287')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (87, N'female', N'Myrtice', N'Holmstrom', N'3016 Trymore Road', N'Owatonna', N'MN', N'55060')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (88, N'female', N'Bambi', N'Cruz', N'1581 Red Maple Drive', N'Irvine', N'CA', N'92618')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (89, N'female', N'Patricia', N'Oneal', N'1630 Cedarstone Drive', N'Findlay', N'OH', N'45840')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (90, N'female', N'Inez', N'George', N'2549 Hope Street', N'Plano', N'TX', N'75074')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (91, N'male', N'Robert', N'Worley', N'3087 John Calvin Drive', N'Schaumburg', N'IL', N'60173')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (92, N'male', N'William', N'Byars', N'2175 Sunburst Drive', N'Lehigh Acres', N'FL', N'33936')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (93, N'male', N'Robert', N'Sullivan', N'4104 Watson Street', N'Camden', N'NJ', N'08102')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (94, N'female', N'Susan', N'Gallegos', N'1422 Comfort Court', N'Madison', N'WI', N'53703')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (95, N'male', N'Joseph', N'Costanzo', N'2375 Abia Martin Drive', N'Huntington Station', N'NY', N'11746')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (96, N'male', N'Matthew', N'Bender', N'231 Pretty View Lane', N'Ukiah', N'CA', N'95482')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (97, N'female', N'Theresa', N'Lomonaco', N'3741 Jessie Street', N'Columbus', N'OH', N'43215')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (98, N'female', N'Elaine', N'Keane', N'463 Coventry Court', N'Baton Rouge', N'LA', N'70815')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (99, N'female', N'Nancy', N'Robertson', N'4616 Holden Street', N'San Diego', N'CA', N'92101')
INSERT [dbo].[fakenames] ([FakeNameID], [gender], [FName], [LName], [Address], [City], [ST], [Zip]) VALUES (100, N'male', N'Henry', N'Smoot', N'1865 Cody Ridge Road', N'Guymon', N'OK', N'73942')
GO
print 'Processed 100 total records'
SET IDENTITY_INSERT [dbo].[fakenames] OFF
So Now we want to shuffle them.
select * from fakenames order by LName, fname
update fakenames
set FName = n.FName, LName = n.LName
from
(
select FName, LName, FName.RowID from
(
select top 100 percent fname, ROW_NUMBER() over (order by newid()) RowID from fakenames order by NEWID()
) as FName
join
(
select top 100 percent lname, ROW_NUMBER() over (order by newid()) as RowID from fakenames order by NEWID()
) as LName on FName.RowID = LName.RowID
)n
join fakenames fn on fn.FakeNameID = n.RowID
--now it is randomized each time you run this.
select * from fakenames order by LName, fname
That should run reasonably fast. It took about 23 seconds with 100,000 rows on my desktop. I assume this is a testing/dev thing that isn't going to be run over and over?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2012 at 12:55 pm
Thanks Sean for taking time to help me out. Now I have two good solutions that I can work with.
January 5, 2012 at 12:57 pm
You're welcome. Let us know which one ends up performing the best of the three. I would be interested to know which one wins that race. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply