寻找生成1000多条记录的SQL脚本 [英] Looking for SQL script that generate 1000+ records

问题描述

这里有没有可用的脚本我可以在我的sql server中导入，其中至少有1000个表，记录等



谢谢，

Sudhir



我尝试过：



我没有尝试任何东西。我需要一个至少有1000个表和记录的数据库，我无法手动创建。所以想到获得帮助

Is there any script available here which I can import in my sql server which has at least 1000 of tables, records, etc

Thanks,
Sudhir

What I have tried:

I did not try anything . I need a database which has got at least 1000 Tables and records which I cannot create manually. So thought of getting the help

推荐答案

这个脚本将创建一个名为'The1000'的数据库和1001个名为T1，T2，T3，... T1000的表，其中包含1000个相同的记录每个，加上一个名为'track'的表 - 其内容应该是大约2092行。

This script will create a database called 'The1000' and 1001 tables named T1, T2, T3, ... T1000 containing 1000 identical records each, plus a table called 'track' - the contents of that should be around 2092 rows.

use master
IF EXISTS(select * from sys.databases where name='The1000')
DROP DATABASE The1000
GO
CREATE DATABASE The1000
GO
use The1000

CREATE TABLE track (id int identity(1,1), dat nvarchar(255))

GO
SET NOCOUNT ON;
	
DECLARE @maxlength int = 30
DECLARE @minlength int = 2
INSERT INTO track
SELECT  
( 
	select top (abs(checksum(newid())) % (@maxlength-@minlength) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A'))  
	from sys.all_objects a1
	where sign(a1.object_id) = sign(t.object_id) /* Meaningless thing to force correlation */
	for xml path('')
) as NewRandomString 
FROM sys.all_objects t;
-- Random text generator from Rob Farley - http://stackoverflow.com/questions/2152204/tsql-pseudo-random-text-generator

DECLARE @start int = 1
DECLARE @end   int = 1000	-- Change this to the number of tables you want
WHILE @start <= @end
BEGIN
	declare @sql nvarchar(max) = 'CREATE TABLE T' + CAST(@start AS NVARCHAR)
	set @sql += '( ID INT, DAT NVARCHAR(255))  '
	set @sql += 'INSERT INTO T' + CAST(@start AS NVARCHAR) + ' SELECT TOP 1000 * FROM track'
	EXEC sp_executeSql @sql
	SET @start += 1
END

如果你想要随机表名，那么使用 dat 表中的列 track 其中 id = @start 而不是。

If you want random table names then use the dat column from the table track where id = @start instead.

你可以使用SQL Server示例数据库

Microsoft SQL Server产品样本：数据库 - 主页 [ ^ ]

根据您的SQL Server版本选择一个。



您可以使用 CROSS JOIN 生成自己的样本数据。

有一些工具可用用于生成测试数据。

SQL数据生成器 - 数据MS SQL Server数据库生成器 [ ^ ]



希望，它有帮助：）

You can use SQL Server sample database
Microsoft SQL Server Product Samples: Database - Home[^]
Choose one according to your SQL Server version.

You can generate sample data of your own using CROSS JOIN.
There are some tools available for generating test data too.
SQL Data Generator - Data Generator For MS SQL Server Databases[^]

Hope, it helps :)

（更新的解决方案）

(updated solution)

-- Create 1000 test tables with 1000 records.
USE [master]
GO

DECLARE @teller1 int;
DECLARE @testname VARCHAR(50);
SET @teller1 = 1;

WHILE @teller1 <= 1000
BEGIN
	SET @testname = 'Test' + STR(@teller1,4);

	BEGIN
		SET @testname = QUOTENAME(@testname);
		EXEC('CREATE DATABASE ' + @testname);
		EXEC('USE ' + @testname + '; CREATE TABLE [Customer](
			[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
			[Name] [nvarchar](128) NULL,
			 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
			(
				[ID] ASC
			)
			) ON [PRIMARY];
			
			DECLARE @teller int;
			SET @teller = 1;
			 
			WHILE @teller <= 1000
			BEGIN
				INSERT INTO [Customer] ([ID],[Name]) VALUES (NEWID(), ''TEST'');
				SET @teller = @teller + 1;
			END;')
	END;

	SET @teller1 = @teller1 + 1;
END

GO

这篇关于寻找生成1000多条记录的SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！