I have an issue with an update of a column with unique random generated values, using SQL 2005.
What I want to achieve is to create unique username for a list of let's say 1000 rows. My approach, which may be wrong, is as follows: to create a view to retrieve a random generated number of 7 digits; to use a function that retrieves the number from the
view and checks if it is unique in the table or not (if it is, the number is returned, if it is not, a new one will be generated and so on); to update the table using that random generated number.
The problem is that it does not work. It does not generate unique numbers, not all the times (you have to test by running the code few times). I suspect it is something with the update, but not sure how to solve it.
Here are the steps to reproduce the problem.
Create the table:
CREATE TABLE [dbo].[TB_Temp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ADM_USERNAME] [nvarchar](7) COLLATE Latin1_General_CI_AS NULL,
) ON [PRIMARY]
Add some test rows:
DECLARE @i INT
SET @i = 0
WHILE @i <= 1000
INSERT INTO TB_TEMP
SET @i = @i +1
Create the view to random generate 7 digits numbers:
CREATE VIEW vw_RandNumberForAdminUserName
SELECT ROUND((9999999 - 1000000 - 1) * RAND() + 1000000, 0) AS RandNumber
Create the function to retrieve the unique 7 digits number:
CREATE FUNCTION [dbo].[fn
View Complete Post