I need to generate some demo data. In this example I have a Customer table that needs to get updated from a table with random values. Trying to create some tsql that can be used if the Customer table has more rows, less rows, or more rows than the random table. I am assuming that each table has a ROW_NUMBER (RECID). The problem is that the way I have it written i have to add a +1 to the WHERE statement depending on which file has more rows. Want to make the tsql flexible enough that one WHERE clause will work for all three examples. Any help is appreciated.
when random_names has more rows than customer
where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt) + 1;when customer has more rows than random_names
where customer.recid%(@random_names_rowcnt) + 1 = random_names.recid%(@customer_rowcnt) ;when customer and random_names have the same number of rows
where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt) ;/ setup example /
IF OBJECT_ID('customer', 'U') IS NOT NULL
drop table customer
create table customer (recid int, cust_name text)
insert into customer (recid) values (1), (2), (3), (4), (5);IF OBJECT_ID('random_names', 'U') IS NOT NULL
drop table random_names
create table random_names (recid int, name text);
insert into random_names (recid, name) values (1,'beth'), (2,'bob'), (3,'gary'), (4,'linda'), (5,'olivia'), (6,'francis'), (7,'simone'), (8,'westmonte'), (9,'gail');/ example #1 - more rows in random table than customer table /
declare @customer_rowcnt int = (select count() from customer);
declare @random_names_rowcnt int = (select count() from random_names);update customer set cust_name = random_names.name
from random_names
where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt) + 1;select * from customer;
/ example #2 - equal number of rows in each table /
delete from random_names where recid > 5;
update customer set cust_name = '';declare @customer_rowcnt int = (select count() from customer);
declare @random_names_rowcnt int = (select count() from random_names);update customer set cust_name = random_names.name
from random_names
where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt);select * from customer;
/ example #3 - more rows in customer table than random table /
delete from random_names where recid > 3;
update customer set cust_name = '';declare @customer_rowcnt int = (select count() from customer);
declare @random_names_rowcnt int = (select count() from random_names);update customer set cust_name = random_names.name
from random_names
where customer.recid%(@random_names_rowcnt) + 1 = random_names.recid%(@customer_rowcnt);select * from customer;