CODEWITHSUNDEEP
sqlselectrandom
Roksana
Roksana

Reputation: 23

Randomly select values fromone column and paste them in another table in sql

I want to randomly select values from one table (1000 rows) and put them in another (4000 rows).

First table is Customer (1000 rows), second table is Customer_Account (4000 rows). They both have a column Customer_ID and Account_ID. In the Customer_Account table, only the Customer_ID column is populated. The Account_ID column of Customer_Account table I want to populate with randomly selected values from the Account_ID column of Customer. This means that the same value from the Customer table may occur multiple times in the Customer_Account table.

This query puts one randomly selected single value from the first table to another 4000 times:

set CustomerID = t2.CustomerID
from Customer_Account t1
cross apply (
    select top 10 CustomerID
    from Customer 
    where CustomerID=t1.CustomerId
    order by NewID()
) t2

Any suggestions on how I can solve this?

Upvotes: 2

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Your question is a bit hard to follow. You say you want to assign account ids, but the code assigns customer ids.

Well, the problem is that the subquery is evaluated only once so you only get one set of values. Instead, enumerate the two tables randomly and use arithmetic to assign the values:

update ca
    set ca.account_id = c.account_id
    from (select ca.*, row_number() over (order by newid()) as seqnum
          from customer_account ca
         ) ca join
         (select c.*, row_number() over (order by newid()) as seqnum,
                 count(*) over () as cnt
          from customer c
         ) c
         on c.seqnum = (ca.seqnum % c.cnt) + 1

Upvotes: 1

SteveC
SteveC

Reputation: 6015

You could use something like this.

with c_cte(CustomerID) as (
    select top(1000) CustomerID
    from Customer
    order by newid())
select c.CustomerID, t4.CustomerID
from c_cte c
     cross apply (select top(4) CustomerID
                  from Customer
                  order by newid()) t4;

To update existing rows in the Customer_Account table with randomly selected CustomerID's from the Customer table you could use something like this

update ca
  set AccountID=c_in.CustomerID
from Customer_Account ca
     cross apply (select top(1) CustomerID
                  from Customer
                  order by newid()) c_in
where ca.AccountID is null;

Upvotes: 0

Related Questions