none
Problem Solving#2: store randomly generated number in a table

    Question

  • Environment: SQL Server 2005 and Win7

    Tools: SQL Studio 2008 R2

    Code:The following code generates randomly one number at a time upon code execution:

    DECLARE @new_nbr CHAR(9)
    
    SELECT @new_nbr = ltrim(rtrim(convert(CHAR(9),convert(int,(RAND()*1000000000))))) 
    
    WHILE EXISTS(SELECT pn_id FROM Table_ssn WHERE pn_id = @new_nbr) OR 
    EXISTS(SELECT pn_id from Tabel_people where pn_id=@new_nbr)
    
    Begin
    SELECT @new_nbr = ltrim(rtrim(convert(CHAR(9),convert(int,(RAND()*1000000000))))) 
    End 
    
    SELECT @new_nbr 

    Table#1

    DECLARE @Table_SSN TABLE 
    (
    ssn CHAR(9) NOT NULL 
    ,pn_ID CHAR(9) NOT NULL
    )

    Table#2

    DECLARE @Table_people TABLE 
    (
    SSN CHAR(9) NOT NULL Primary key
    pn_id CHAR(9) NOT NULL Reference @table_people (pn_id)
    ,first_nm VARCHAR(10)
    ,last_nm VARCHAR(20)
    )

    Table#3

    DECLARE @Keygen_stored_data TABLE 
    (
    ID Identity(1,1) not null , pn_ID CHAR(9) UNIQUEIDENTIFIER NOT NULL
    )

    Problem: I would like to generate a set of randomly generated numbers then store them in a table. The table has two fields: keygen and ID. The key generator can be determined by the number of records , which can be counted in the flat file. The flat file source, contains large data, but here let say nine records in a csv format. The attributes are [SSN], [First name], and [Last name] listed in order:

    '001456789',  'Jim', 'Kramer';
    '008610321', 'Samanta', 'Cohen',
    '097754378', 'Ray', 'elvicio'
    '112233350', 'Mike', 'Smith', 
    '123456789', 'Dana', 'Kobo', 
    '123456789', 'Dalia', 'Budd', 
    '987654321', 'George', 'Jackson', 
    '987654321', 'Pamella', 'Slattery', 
    '987654321', 'Jose', 'Slattery'

    Expected results:

    ID | Keygen

    1  | 404871311

    2  | 745022946

    3  | 747824407

    ...

    ..

    .

    9 | 206536992

    My attempts have failed and I wonder if there is an outbox approach to attain the expected results


    • Edited by Sandra VO Monday, June 24, 2013 11:36 PM grammar
    Monday, June 24, 2013 11:36 PM

Answers

All replies

  • Something like this might work, requires a Numbers table.. but that is handy to have around anyway.  I use NEWID() for the seed, it seems to produce more random numbers.

    DECLARE @Table TABLE (Id INT IDENTITY, KeyGen CHAR(9))

    --preload values
    INSERT INTO @Table(KeyGen)
    SELECT TOP 1000 CAST(10000000 + CONVERT(INT, (99999999-10000000+1)*RAND(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))) AS CHAR(9))
    FROM Numbers n

    Tuesday, June 25, 2013 12:16 AM
  • Expected results:

    ID | Keygen

    1  | 404871311

    2  | 745022946

    3  | 747824407

    ...

    ..

    .

    9 | 206536992

    My attempts have failed and I wonder if there is an outbox approach to attain the expected results


    Try something like below:

    CREATE TABLE[STUDENT1](
    	[STUDENT_NAME] [VARCHAR](10) NULL,
    	[MARKS] [INT] NULL,
    	[GRADE] [VARCHAR](10) NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('TODD', 52,'E')
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('JESSE', 48,'E')
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('KRISHNA', 77,'A')
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('KRISHNA', 77,'A')
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('BALA', 95,'A')
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('MANOJ', 85,'A')
    INSERT [DBO].[STUDENT1] ([STUDENT_NAME], [MARKS], [GRADE]) VALUES ('KRISH', 75, 'A')
    
    
    SELECT * FROM [STUDENT1]
    -- WITH CTE(COMMON TABLE EXPRESSION)
    WITH CTE
    AS
    (
      SELECT ROW_NUMBER() OVER(ORDER BY MARKS) AS RANDOM_NUMBER,* FROM [STUDENT1]
    )
    SELECT * FROM CTE
    
    -- WITH TEMP TABLE
      
      SELECT ROW_NUMBER() OVER(ORDER BY MARKS) AS RANDOM_NUMBER,*
      INTO #TEMP FROM [STUDENT1]
      SELECT * FROM #TEMP

    Thanks.


    bala krishna

    Tuesday, June 25, 2013 4:32 AM
  • @SDeering and @bala krishna: Thanks for taking the time to write the TSQL script but it doesn't provide the expected results.

    The answer is here

    • Edited by Sandra VO Monday, March 17, 2014 11:17 PM resolved
    • Marked as answer by Sandra VO Monday, March 17, 2014 11:17 PM
    Thursday, June 27, 2013 11:33 PM