mssql sqlserver 生成大量测试数据的方法分享


摘要:
当我们进行数据调优时,通常需要大数据量,但是此时我们手上有没有大数据样本,下文将分享大数据样本的建立方法


如果需下载实例数据库 AdventureWorks2012
如果没有,请从 https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
下载
例:
生成一个数据表testInfo ,表拥有1000行数据,如下所示:
declare @rowsmax bigint
set @rowsmax =1000 —-设定1000行数据

 CREATE TABLE testInfo(OrderID BigInt Primary Key,
CustomerID BigInt,
FirstName Varchar(100),
LastName Varchar(100),
ProductID Bigint,
UnitPrice Decimal(10,2),
OrderAmount Decimal(10,2),
OrderDate Datetime,
ShipDate Datetime)
GO

--插入数据
declare @rowsmax Bigint =1000
;With CTE AS (
			Select Row_Number() Over (Order BY A.BusinessEntityID) CustomerID ,
				   A.FirstName ,
				   B.LastName 
			FROM person.person A CROSS JOIN person.person B)
INSERT INTO testInfo(OrderID,CustomerID,FirstName,LastName,ProductID,UnitPrice,OrderAmount,OrderDate,ShipDate )
SELECT distinct OrderID,X.CustomerID,FirstName,LastName,ProductID,UnitPrice,OrderAmount,OrderDate,ShipDate 
FROM (SELECT Row_Number() Over (Order BY cAST(A.CustomerID AS BIGINT)) OrderID,A.*,SalesOrderID,ProductID,B.UnitPrice,LineTotal OrderAmount 
FROM CTE A CROSS JOIN   sales.salesOrderDetail B ) X
JOIN [Sales].[SalesOrderHeader] C ON C.SalesOrderID =X.SalesOrderID
WHERE OrderID<=@rowsmax