Need help running script with large record size

[ruckrock]

Hi,

I currently am building script to randomize all a table of patient data. The table has over 170,000 records in it and my script is having problems finishing. If I use it on 40,000 or less records, it will work fine. If i try it on all the records, it goes on forever. Can anyone see anything glaring sticking out to the in my code that would cause this to happen?

Here is my code thus far, it hangs on part where is randomizes Last Names:


SELECT *
INTO #temppat1
FROM patient
WHERE ISNUMERIC(patient_id) = 1
order by newid()


CREATE TABLE #LastNameList
(
ListId INT IDENTITY(1,1),
patient_id char(8),
last_name VARCHAR(20)
)

DECLARE @MaxListId INT
DECLARE @counter1 INT
DECLARE @addy INT
DECLARE @mainstreet CHAR



INSERT INTO #LastNameList
SELECT patient_id, last_name
FROM #temppat1

SET @counter1 = 1
SET @addy = 100
SET @mainstreet = 'Main Street'

SELECT @MaxListId = MAX(ListId) FROM #LastNameList

DECLARE @counter2 INT
SET @counter2 = 0

WHILE @counter2 in (0,1,2) OR @counter2 >= @MaxListId
BEGIN
SET @counter2 = RAND()*10
END

DECLARE @NewLastName VARCHAR(10)
DECLARE @CurrentPatientId INT

WHILE @counter1 <= @MaxListId
BEGIN

SELECT @CurrentPatientId = patient_id
FROM #LastNameList
WHERE ListId = @counter1




SELECT @NewLastName = last_name
FROM #LastNameList
WHERE ListId = @counter2

SET @counter1 = @counter1 + 1
SET @addy = @addy + 1

IF @counter2 >= @MaxListId
BEGIN
SET @counter2 = 0
END
SET @counter2 = @counter2 + 1

UPDATE #temppat1
SET last_name = @NewLastName,
address = rtrim(convert(char(20),@addy)) + ' ' + 'Main Street'
WHERE
patient_id= @CurrentPatientId
END

 

[Will192]

A couple of questions:

1. Is there any indexes on the table being inserted into?
2. Is it possible to insert batches of 40k a shot?
3. What is the size of your tempdb?
4. What are your specs on your server?

Live to Throw
Throw to Live

 

[ruckrock]

THanks for the reply....I ended up figuring this one out a couple weeks ago. The customer data table that I was using had over 200k records in it, but I was able to filter out the records I didnt need before I started looping, which seemed to have solved the problem. The script still took about 5mins to run, but I guess it was doing alot of work in those loops.

Thanks for the response, though...I'm sure I will need more help in the near future