StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I wasn't sure if you really wanted an update query or a select query. The following query returns a new operator for each order, subject to your conditions:</p> <pre><code>/* with orders as (select 1 as orderId, 'order1' as orderDesc, 1 as OperatorId), operators as (select 1 as operatorID, 'John' as name) */ select o.*, op.name as NewOperator, op.operatorID as NewOperatorId from (select o.*, (ROW_NUMBER() over (order by newid()) % numoperators) + 1 as randseqnum from Orders o cross join (select COUNT(*) as numoperators from operators) op ) o join (select op.*, ROW_NUMBER() over (order by newid()) as seqnum from Operators op ) op on o.randseqnum = op.seqnum order by orderid </code></pre> <p>It basically assigned a new id to the rows for the join. The order table gets a value between 1 and the number of operators, randomly assignd. This is then joined to a sequence number on the operators.</p> <p>If you need to update, then you can do something like:</p> <pre><code>with toupdate as (<above query>) update orders set operatorid = newoperatorid from toupdate where toupdate.orderid = orders.orderid </code></pre> <p>Your two questions:</p> <p><em>Will it be better to first select all all orders and all operators that meets the conditions to temporary table and then do the shuffling or to do it all in one big query?</em></p> <p>The user of temporary tables is a matter of performance and requirements for the application. If the data is being rapidly updated, then yes, using a temporary table is a big win. If you are running the randomization many, many times on the same data, then it can be a win, particularly if the tables are too big to fit in memory. Otherwise, there is not likely to be a big performance gain on a one time run, assuming you put the conditions within the innermost subqueries. However, if performance is an issue, you can test the two approaches.</p> <p><em>I would like to pass array or groups as a parameter to my procedure. Which option would be the best to pass array to stored procedure (SQL Server 2005).</em></p> <p>Hmmm, switch to 2008 which has table valued parameters. Here is a highly reference article on the subject by Erland Sommarskog: <a href="http://www.sommarskog.se/arrays-in-sql-2005.html" rel="nofollow">http://www.sommarskog.se/arrays-in-sql-2005.html</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload