Use random string variable in a insert

Question

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?

Answer 1

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

• Racer SQL: Thank you for your answer. I was lokking for something like this. With top variable, and row_number() with a variable too.

Answer 2

insert into dbo.tblexecution (
 fname
 ,lname
 ,brtday
)
select top 100 
 [FirstName]
 ,[LastName]
 ,convert(date, dateadd(day, (abs(CHECKSUM(newid())) % 3650) * - 1, getdate()))
from [AdventureWorks2008R2].[Person].[Person]
order by newid()

• Racer SQL: Thank you for Your answer, but this is exactly what I have.