Inserting Random Data Pairs from one table to ano

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting Random Data Pairs from one table to ano

Author

Topic

mrfleck
Starting Member

2 Posts

Posted - 2002-11-11 : 19:56:45

This should be pretty easy, but I am having trouble running this out.

I have two tables one has 6000 records that amount to personal information. I want to replace the homePhone,workPhone,code1,fname information randomly from a table that has ~ 50 rows of dummy data.

Table 1
recID,fname,lname,address,city,state,homePhone,workPhone,code1

Table 2
fname,homePhone,WorkPhone,code1

Table 2 has ~ 50 records and I want an even distribution of these in the first table. In table two, the fname,homePhone,WorkPhone,code1 have to go as a group, the fname is related to the numbers.

Seems easy, but I cannot get it this afternoon, any help would be appreciated.

He is a sample of getting a random row from table 2 that I am using:

SELECT TOP 1 fname,homePhone,workPhone,code1 FROM table2 ORDER BY NEWID()

This gets a random row, now I need to update each row in table one with this info. Basically I need to loop through table one, updating those fields with the results of: SELECT TOP 1 fname,homePhone,workPhone,code1 FROM table2 ORDER BY NEWID()

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-11 : 22:49:09

Tell you what, I don't know everything you need, but here is a very simple/easy algorithm that you can implement on your own.

Since you only have 6000 records, this should be quick and easy to do. You'll have to insert some code, give the proper types, and I'm guessing on your primary keys.

First, introduce a sequence number and number your dummy data in Table 2 from 0 to 49.

Next, create a temp table that consists of the primary key of your employee table, and an identity column. Insert all your employee primary key's into the table ordered by newid(). This gives your rows a random sequence.

Take a look at this join condition:
FROM #Temp INNER JOIN Table2 ON (ident % 50 = seq)
ident is your identity column introduced in the temp table. This associates a random row from Table 2 with each primary key. From there, it is a simple UPDATE using the SQL Server specific JOIN syntax to update the records.


UPDATE E
SET E.fname = dt.fname.......
FROM Table1 E
  INNER JOIN (SELECT * FROM #Temp INNER JOIN Table2 ON (ident % 50 = seq)) dt
   ON (E.recID = dt.recID)

I would suggest also creating an index on the temp table to speed up the join.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 11/11/2002 22:49:42

mrfleck
Starting Member

2 Posts

Posted - 2002-11-12 : 17:13:30

Thanks a bunch, worked like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources