Looking for SQL script that generate 1000+ records

Question

1.00/5 (3 votes)

See more:

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

Posted 15-May-17 21:31pm

Member 13203142

Updated 15-May-17 23:39pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-05-15T23:39:00

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.

SQL

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

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

Suvendu Shekhar Giri · Answer 2 · 2017-05-15T22:00:00

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 :)

RickZeeland · Answer 3 · 2017-05-15T22:33:00

(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

Looking for SQL script that generate 1000+ records

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0