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
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