CODEWITHSUNDEEP
sql-serverrandomsql-insert
silverbackbg
silverbackbg

Reputation: 182

Insert random Data content in SQL Server 2008

I know there are several topics on this, but none of them was suitable for me, that's why I took the chance to ask you again.

I have a table which has columns UserID, FirstName, Lastname.

I need to insert 300 000 records for each column and they have to be unique, for example:

UserID0001, John00001, Doe00001
UserID0002, John00002, Doe00002
UserID0003, John00003, Doe00003

I hope there is an easy way :)

Thank you in advance.

Best, Lyubo

Upvotes: 3

Views: 7940

Answers (3)

i-one
i-one

Reputation: 5120

;with sequence as (
    select N = row_number() over (order by @@spid)
    from sys.all_columns c1, sys.all_columns c2
)
insert into [Table] (UserID, FirstName, Lastname)
select
    'UserID' + right('000000' + cast(N as varchar(10)), 6),
    'John' + right('000000' + cast(N as varchar(10)), 6),
    'Doe' + right('000000' + cast(N as varchar(10)), 6)
from sequence where N <= 300000

Upvotes: 3

Jesse Smith
Jesse Smith

Reputation: 963

I've adapted a script found in this article:

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(14)
DECLARE @First VARCHAR(14)
DECLARE @LAST VARCHAR(14)
DECLARE @ID VARCHAR(14)
SET @ID = 'UserID'
SET @First = 'John'
SET @Last = 'Doe'
SET @RowCount = 1
WHILE @RowCount < 300001
BEGIN
    SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SET @RowString = REPLICATE('0', 6 - DATALENGTH(@RowString)) + @RowString
    INSERT INTO TestTableSize ( 
         UserID  
        ,FirstName  
        ,LastName  
        ) 
    VALUES  
        (@ID + @RowString  
        , @First + @RowString  
        , @Last + @RowString)  
    SET @RowCount = @RowCount + 1 
END

Upvotes: 1

Sebastian Meine
Sebastian Meine

Reputation: 11793

You could use the ROW_NUMBER function to generate different numbers like this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.users(
  Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  user_id VARCHAR(20),
  first_name VARCHAR(20),
  last_name VARCHAR(20)
);

GO
DECLARE @NoOfRows INT = 7;

INSERT INTO dbo.users(user_id, first_name, last_name)
SELECT 'User_'+n, 'John_'+n, 'Doe_'+n
FROM(
SELECT REPLACE(STR(ROW_NUMBER()OVER(ORDER BY (SELECT NULL))),' ','0') n FROM(
select TOP(@NoOfRows) 1 x from sys.objects A,sys.objects B,sys.objects C,sys.objects D,sys.objects E,sys.objects F,sys.objects G
)X
)N

Query 1:

SELECT * FROM dbo.users

Results:

| ID |         USER_ID |      FIRST_NAME |      LAST_NAME |
-----------------------------------------------------------
|  1 | User_0000000001 | John_0000000001 | Doe_0000000001 |
|  2 | User_0000000002 | John_0000000002 | Doe_0000000002 |
|  3 | User_0000000003 | John_0000000003 | Doe_0000000003 |
|  4 | User_0000000004 | John_0000000004 | Doe_0000000004 |
|  5 | User_0000000005 | John_0000000005 | Doe_0000000005 |
|  6 | User_0000000006 | John_0000000006 | Doe_0000000006 |
|  7 | User_0000000007 | John_0000000007 | Doe_0000000007 |

Just change the @NoOfRows to 300000 to get the number of rows you are looking for.

Upvotes: 3

Related Questions