SQL Server – How to Use Random String Variable in an Insert

randomsql serversql-server-2008-r2

This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly.

I have this for now:

declare @date date,
        @val1 int,
        @val2 int
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()))

    insert into tblexecution ( fname,lname,brtday )
    values ('name1','Name2',@date)

This way, I need to change manually the name1 and name2.

is there a way to use, values of another table, in a variable, for this table? I don't know how to explain it but I have a table called Adventureworks2008.person.person (you guys know this database) with a lot of random names ( Fields > FirstName,LastName ), and I want to use this values, in my @val1 and @val2 variables, to be something like this:

declare @date date,
        @val1 int,
        @val2 int
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1,
 getdate()))

    insert into tblexecution ( fname,lname,brtday )
    values (@val1,@val2,@date)

so I just loop ( or press f5 a lot ).

Edit1:

With this, I have at least a random int:

select right(rand(),2)

Edit2:

I could do this, But I think this is a poor job:

insert into  execution..tblexecution
select distinct  firstname, lastname,
    convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1,getdate())) as brtday
    from adventureworks2008.person.person

Edit3:

I could make this:

declare @date date,
        @val1 nvarchar(200),
        @val2 nvarchar(200)
set @date = convert(date,dateadd(day, (abs(CHECKSUM(newid())) % 3650) * -1, getdate()));
set @val1=(select  top 1 firstname
               from adventureworks2008.person.person
              order by newid() );
set @val2=(select top 1 lastname
               from adventureworks2008.person.person
              order by newid() )

    insert into tblexecution ( fname,lname,brtday )
    values (@val1,@val2,@date)

is this the best way to use random variables in a query?

Best Answer

First of all the answer that @byrdzeye is the best answer based on your question. Assuming that you don't specifically need the firstname and lastname to be randomly put together.

If you specifically need each column to be random you can try something like this:

-- Set up a temp table to insert into
SELECT TOP 0 firstname fname, lastname lname, getdate() AS brtday 
    INTO #tblexecution 
FROM [Person].[Person]

-- Actual code
DECLARE @Rows INT = 1000

INSERT INTO #tblexecution (fname, lname, brtday)
SELECT 
     f.[FirstName] ,l.[LastName]
    ,convert(date, dateadd(day, (abs(CHECKSUM(newid())) % 3650) * - 1, getdate()))
FROM (SELECT TOP (@Rows) row_number() OVER (ORDER BY newid()) AS Id, firstname 
          FROM [Person].[Person]) f
JOIN (SELECT TOP (@Rows) row_number() OVER (ORDER BY newid()) AS Id, lastname 
          FROM [Person].[Person]) l
    ON f.id = l.id

This performed pretty well in my test and scaled well up to the limits of Adventureworks. (Just under 20k names took under 1 second on my box.)

Related Question