score:3
Accepted answer
;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
Read More
- Insert random Data content in SQL Server 2008
- Generate random SQL Server 2008 time test data
- Insert data in to table using xml and stored procedure in SQL Server 2008
- best way to insert data using dephi in sql server 2008
- How to insert large amount of data into SQL Server 2008 with c# code?
- Insert data within multiple tables in SQL Server 2008
score:1
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
score:3
You could use the ROW_NUMBER function to generate different numbers like this:
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
| 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.
More questions
- How to insert xml data from URL into SQL Server 2008 database by call stored procedure in C#
- How to split a string in sql server 2008 using stored procedure and insert the data to table
- Iterate through rows and insert data in temp table - SQL Server 2008
- How to insert Current Date Automatically while inserting data into SQL Server 2008
- Bulk insert into SQL Server 2008 R2 from XML RAW data
- whats the fastest way to insert streaming data into a table, DB is MS SQL server 2008
- Insert data from stored procedure returning mutiple dataset into a temporary table in SQL Server 2008
- Error While Inserting large amount of data using Insert statement in SQL Server 2008
- Exporting data In SQL Server as INSERT INTO
- Backup a single table with its data from a database in sql server 2008
- How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
- How to insert a data table into SQL Server database table?
- What's the fastest way to bulk insert a lot of data in SQL Server (C# client)
- UPDATE if exists else INSERT in SQL Server 2008
- In SQL Server 2008 R2 script data missing on Script Wizard
- INSERT XML into SQL Server 2008 database
- How to insert binary data into sql server using SSMS
- Insert varbinary data into SQL Server database
- How to insert data into SQL Server
- How to generate random data in SQL server
- MS SQL Server with management studio - how to script a table with its data (as insert statements)?
- SQL Server 2008 Row Insert and Update timestamps
- How to Insert xml data into SQL Server table?
- How can I Insert data into SQL Server using VBNet
More questions with similar tag
- Back up database without making it to read-only
- Debugging stored procedures in SQL Server Management Studio
- Implications of using Serializable and Snapshot isolation concurrently
- How do I natively translate SqlType to underlying SQL type declaration?
- Is a sort of foreach statement possible in t-sql?
- count the total number of column field appeared more than once in database
- Fastest way to get Id or insert value from Microsoft Sql
- Log Sql server stored procedure start and end time
- Randomize existing date's day and month in tsql
- Sub CTE or Nested Model
- t-sql join query for a view
- Large sets of sql parameters in query
- One database field is not update in EntityFramework
- How to query values from XML without nodes?
- Performing interpolation on a table, grouping by a 3rd field
- Default sort order for a select query in SQL Server 2005 and SQL Server 2012
- C# Passing parameters to stored procedure with EXEC query
- Very strange SSIS error. DTS_E_PRIMEOUTPUTFAILED
- DATEFORMAT FUNCTION IN SQL
- how to store multi row query result in a variable in SSIS
- Return id where count of value associated with that id on other table is 0
- SQL Server trigger inserting whole table rows, instead of new
- SQL Server 2008
- MS SQL - Fastest way to adding $2 to a varchar column with numbers
- Using an inner join with subqueries in an update syntax
- SQL Server scalar function vs. subquery execution plan analysis
- Entity Framework and SQL Server Synonyms
- {|Form.Title|} not displaying form title in email subject of a custom sitefinity form
- What is a better alternative to Excel for loading data to a SQL Server database?
- prevent SQL Server sproc from returning result set if select statement errors
Source:
stackoverflow.com