CODEWITHSUNDEEP
sql-server
markzzz
markzzz

Reputation: 47945

How to choose a random ID from a table and use it for populate random data to another table?

I'm trying to insert random data to do a stress test for my application, following this guideline.

I've some foreign key on some columns, and I want to insert some random data for those too.

Let say table Clinics have 5 records, with ID "3,7,90,98,102"; for each insert on Activities I'd like to pick one of those 5 IDs. Tried with:

Declare @clinicId int

Declare @counter int
Set @counter = 11

While @counter < 12
Begin 
    @clinicId = SELECT TOP 1 * FROM Clinics ORDER BY NEWID()

   Insert Into Activities values (@clinicId, 100, 10, 90, 2, 65, 1)

   Set @counter = @counter + 1
End

But it says Syntax error in proximity of @clinicId. Any clues?

Upvotes: 0

Views: 75

Answers (2)

IVNSTN
IVNSTN

Reputation: 9299

Your code can be fixed this way:

set @clinicId = (SELECT TOP 1 clinic_id from ...)

Or you can get rid of loop and variables by set-based solution (supposed to replace all script given):

declare @cnt int = 12

Insert Into dbo.Activities (...)
SELECT
  c.clinicID, 100, 10, 90, 2, 65, 1
FROM 
(
   SELECT TOP (@cnt) 1 dummy
   from master.dbo.spt_values
) v
CROSS APPLY (
  SELECT TOP (1) c.clinicID
  FROM dbo.Clinics c
  ORDER BY NEWID()
) c

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need top (1) prior to assignment :

SELECT TOP (1) @clinicId = ID 
FROM Clinics 
ORDER BY NEWID()

Note :

  • You can assign only single column value to scalar variable.

If you want to assign all data, then you use table variable

Upvotes: 2

Related Questions