IT Blog

  • Blog
  • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. 首页
  2. Technology
  3. Database
  4. SQL Server
  5. 正文

Generating Test Data with SQL Scripts

2021-01-20 64点热度 0人点赞 0条评论
Loading...

<<< These scripts were tested on MS SQL Server  2019 >>>

From time to time we need to generate huge data for testing. Without any data generating tool we can achieve it by using some SQL Scripts. these scripts are different from regular query scripts.

Generate list of data

CREATE TABLE #accounts ( fname VARCHAR(20), lname VARCHAR(20))
GO
INSERT #accounts VALUES ('Fred', 'Flintstone')
GO 1000
SELECT TOP 10 * FROM #accounts
fname lname
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
Fred  Flintstone
ALTER TABLE #accounts ADD id INT IDENTITY(1,1)
SELECT TOP 10 * FROM #accounts
GO
fname lname      id
Fred  Flintstone 1
Fred  Flintstone 2
Fred  Flintstone 3
Fred  Flintstone 4
Fred  Flintstone 5
Fred  Flintstone 6
Fred  Flintstone 7
Fred  Flintstone 8
Fred  Flintstone 9
Fred  Flintstone 10
ALTER TABLE #accounts ADD id INT

DECLARE @id INT
SET @id = 0
UPDATE #accounts
SET @id = id = @id + 1
OPTION ( MAXDOP 1 )
GO

SELECT * FROM #accounts
GO
fname lname      id
Fred  Flintstone 1
Fred  Flintstone 2
Fred  Flintstone 3
Fred  Flintstone 4
Fred  Flintstone 5
Fred  Flintstone 6
Fred  Flintstone 7
Fred  Flintstone 8
Fred  Flintstone 9
Fred  Flintstone 10

Note: MAXDOP is max degree of parallelism, set it to 1 to avoid duplicate value if running on multiple processors machine. Read article 1 for detail.

Generate sequence numbers

Using sys.all_columns table

SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns

Generating Test Data with SQL Scripts

Generate huge Rows using CROSS JOIN

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

Note: the number of rows being generated are depending on the sys.all_columns table size. It took 1 minute to generate 30 million rows in my machine. You could use top n to limit the number of rows being generated.

Direct generation

SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n) 
select * from x

Generating Test Data with SQL Scripts

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1

Generating Test Data with SQL Scripts

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN 1000 AND 2000
ORDER BY 1

Generating Test Data with SQL Scripts

DECLARE @startnum INT=1000, @endnum INT=1050
;
WITH gen AS (
SELECT @startnum AS num
UNION ALL
SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT * FROM gen
option (maxrecursion 10000)

Generating Test Data with SQL Scripts

Generate random numbers

IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL DROP TABLE #SomeTestTable

DECLARE @NumberOfRows INT = 1000000,
        @StartValue   INT = 400,
        @EndValue     INT = 500
DECLARE @Range        INT = @EndValue - @StartValue + 1;
SELECT TOP (@NumberOfRows)
        SomeRandomInteger =  ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
        SomeRandomFloat   = RAND(CHECKSUM(NEWID())) * @Range + @StartValue
INTO #SomeTestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

select * from #SomeTestTable order by 1

Generating Test Data with SQL Scripts

Summary

We could use system table to generate huge number of custom data by using CROSS JOIN on system tables. We also can generate huge number of random data by just using a formula. To limit the number of data being generated we can use SELECT TOP clause.

By the way, "Tally" table is another good way to generate data, more detail read this article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Reference

1. Populate a SQL Server column with a sequential number not using an identity
2. What MAXDOP setting should be used for SQL Server
3. How to generate a range of numbers between two numbers?

error
fb-share-icon
Tweet
fb-share-icon
IT Team
Author: IT Team

Loading...
标签: Blog
最后更新:2021-01-20

IT Team

stay absorbed stay excellent

点赞
< 上一篇

文章评论

取消回复
最新 热点 随机
最新 热点 随机
Controling Category List on Sidebar Widget Restoring the Links Manager Adding reCaptcha for user forms in WordPress Scheduling Background Job in WordPress WordPress database access with $wpdb CSS Tricks
Scheduling Background Job in WordPressRestoring the Links Manager恢复链接管理器AdSense合规指南Controling Category List on Sidebar WidgetAdding reCaptcha for user forms in WordPress
Using widget components in any place OneDrive for Business 文件到 Google Drive 文件 Crawling images from web with PHP Fixing issue on chineseacupuncture.ca Social media Make up table definition with [yr-mth] columns and column list
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • Digital Marketing
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.