Insert N random values into table

I need to insert @N rows with random float values into one table and use IDs of every new inserted row for another INSERT. All this I need to do in stored procedure. For example:

CREATE PROCEDURE Proc
    @N int
AS

-- START LOOP, REPEAT @N TIMES
INSERT INTO [T1]
    ([Value])
VALES
    (<random_float>)

INSERT INTO [T2]
    ([ValueID])
VALUES
    (@@IDENTITY)
-- END LOOP

END
GO

Thanks in advance.

  • Changing SQL Server collation to case insensitive from case sensitive?
  • SQL Group by Client Location
  • Sort by minimum value of two columns
  • Stored Procedure tracking
  • How do I insert information in a SQL table if the table has a foreign key in it?
  • SQL Server export to Excel with OPENROWSET
  • 3 Solutions collect form web for “Insert N random values into table”

    No loops, one insert

    ;WITH cte AS
    (  --there are easier ways to build a numbers table
       SELECT
           ROW_NUMBER() OVER (ORDER BY (select 0)) AS rn
       FROM
          sys.columns c1 CROSS JOIN sys.columns c2 CROSS JOIN sys.columns c3
    )
    INSERT INTO [T1] ([Value])
    OUTPUT INSERTED.ID INTO T2  -- direct insert to T2
    SELECT RAND(CHECKSUM(NEWID()))
    FROM cte
    WHERE rn <= @N;
    

    It’s not entirely clear what you are trying to do. Did you mean something like this:

    create table dbo.RandomTable
    ( 
        rowid int not null PRIMARY KEY,        
        pure_random float null,
    )
    
    declare @row int
    set @row = 1
    while (@row <= @N)
    begin
       insert into dbo.RandomTable (rowid, pure_random)
       values (@row, rand())
       set @row = @row + 1
    end
    

    [I’m not advocating the use of a loop; it’s not the most efficient way of doing this. It’s just that’s the form the poster was asking for…]

    CREATE TABLE [T1]
    (
        [ValueID] INT IDENTITY(1,1),
        [Value] FLOAT
    )
    GO
    
    CREATE TABLE [T2]
    (
        [ValueID] INT
    )
    GO
    
    CREATE PROCEDURE [Proc]
        @N int
    AS
    BEGIN
        DECLARE @i INT = 0;
    
        WHILE (@i < @N)
        BEGIN
            INSERT INTO [T1]
                ([Value])
            SELECT RAND()
    
            INSERT INTO [T2]
                ([ValueID])
            VALUES
                (SCOPE_IDENTITY())
    
            SET @i += 1
        END
    END
    GO
    
    TRUNCATE TABLE T1
    TRUNCATE TABLE T2
    EXEC [Proc] @N = 10
    

    See WHILE (Transact-SQL), and SCOPE_IDENTITY (Transact-SQL).

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.