I'm using Microsoft SQL Server 2005.
I'm creating a random record generator that will insert 10 records randomly into a temporary table. The records in the temporary table will then be used to update records in the table in memory.
Here is the statement that is giving me some troubles (assume the temp table is already created).
insert into #tempTable
select top (10 - @totalOverShort)
d.depositid, d.location, d.amount, d.count, d.user_add,
d.date_add, d.status, d.comments, d.subtotal_difference, d.count_difference, d.user_checked,
d.date_updated, d.ip_checked, newID() as randomColumn
from
closing_balance..cb_depositbag as d
left join
#tempTable as t on d.depositid <> t.depositid and d.location <> t.location
where
d.date_add between @weekPrior and @weekPriorNight and
d.status = 'R' and d.depositID <> t.depositID
order by
randomColumn
This statement does give me randomly generated columns, but sometimes (about 1/10) I get 1 or more duplicates in the temp table. The table in memory has a 2-part key, DepositID and Location. I want to say that if there does not exit a randomly selected (depositID,location) pair, then insert that record into the temp table. Is there another way to do this? I'm thinking the reason that there are duplicates is because it's evaluating the select statement upwards of 10 times, which, since it's being randomly ordered, can cause a duplicate. I'm not sure though.
Thanks in advance.