Randomizing SQL data with data from table function

Question

I trying to make a sql script that will randomize the city, state, and zip code of a "members" table. I have made a table function that returns a single row with columns "city", "state" and "zip" taken from another database at random (via a view). This ensures that I get a city, state, and zip that actually correlate to each other in the real world.

From there I am trying to do something like this:

update t
set 
t.City = citystate.city,
t.State = citystate.state,
t.PostalCode = citystate.zip

from
(select
 City, 
 State,
 PostalCode from DATABASE.dbo.Member) t,
 DATABASE.dbo.getRandomCityState() citystate

Problem is, this only calls my function once, and puts the same city, state, and zip into every row of the table. Is there some way to call my function once for every row in the table?

Who on earth is blindly downvoting decent questions? (+1 to negate it.) — Mike Hofer
– Mike Hofer, Commented Dec 20, 2011 at 20:28

Joe Stefanelli · Accepted Answer · 2011-12-20 20:26:08Z

1

Use a CROSS APPLY

update t
set 
t.City = citystate.city,
t.State = citystate.state,
t.PostalCode = citystate.zip

from
(select
 City, 
 State,
 PostalCode from DATABASE.dbo.Member) t
 CROSS APPLY
 DATABASE.dbo.getRandomCityState() citystate

answered Dec 20, 2011 at 20:26

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mtmurdock Over a year ago

Didn't work. I just ran that and it put the same values into every row.

mtmurdock · Accepted Answer · 2011-12-20 21:08:10Z

1

Ok so thanks to one of my co-workers, we found a solution. It would seem that since the function didn't take any parameters, SQL Server decided that the result would never change. So we tricked the server into thinking it will be different every time by passing a parameter to the function that was different: the id of each row. Since we were passing a different parameter each time, it called the function for every row.

update t 
set 
t.City = citystate.city,
t.State = citystate.state,
t.PostalCode = citystate.zip

from
(select top 10 
City, 
 State,
 PostalCode from TrajectoryServicesTest.dbo.Member) t
 cross apply SanitizePhi.dbo.getRandomCityState(t.MemberID) citystate

Kinda hacky, but it worked. Thanks to Joe for the help.

answered Dec 20, 2011 at 21:08

mtmurdock

13.1k21 gold badges68 silver badges108 bronze badges

2 Comments

Ben English Over a year ago

Look into Detirministic vs Non-Detirministic Functions. You want a non-detirministic function. In tsql you can 'force' a user defined function to be non-detirministic by including a function call to a non-detirministic function like CURRENT_TIMESTAMP or RAND. msdn.microsoft.com/en-us/library/ms178091.aspx

mtmurdock Over a year ago

Thats a great idea. Just a note, you cannot call RAND from a scalar-valued function. I've been dealing with it all day. The solution we ended up using was putting the RAND call into a view.

Collectives™ on Stack Overflow

Randomizing SQL data with data from table function

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related