Filling all your tables with test data

Problem

Database Assessment process for any new project or any new database have more parts one of this parts Assess the most hitting stored procedures and do stress test on it to be sure it can afford as example 500 concurrent user  without any issue in the IO , CPU and execution time , for doing this part we should have reasonable volume of  data in the table used in stored procedures , from two days i received request by new Database should be deploy in production at this time we start our work on the database to do Full database assessment process and when i arrive to the point of filling the table i found 50 tables should be filling by one milion of record  for test.

Solution :

i do one Stored procedure for filling any table with any schema design and this stored procedures covered the most worst scenario like the below cases but this table should have minimum one record 

1. covered the tables with identity columns
2. covered the tables with computed columns
3. covered the tables with Primary key columns and not identity
4. covered the tables with Primary key columns with data type uniqueidentifier
5. covered the table build on different schema
6. covered the table with special column name as example column name KEY

by this script we can fill any volume of data in any table .

—Exec FillingTable ‘FW’,’QuickSettings’,’1000′
IF EXISTS ( SELECT *
FROM sys.objects
WHERE type = ‘P’
AND name = ‘FillingTable’ )
DROP PROCEDURE FillingTable
GO

CREATE PROC FillingTable
@SchemaName NVARCHAR(100) = ‘dbo’ ,
@TableName NVARCHAR(MAX) ,
@batchCount NVARCHAR(200)
AS
BEGIN

— =============================================
— Author: Mustafa EL-Masry
— Create date: 11/02/2015
— Community:https://mostafaelmasry.wordpress.com/
— Description: Filling any Tables with any design with test data
— Notes: i suggested to drop foreign key before you Execute this SP
— =============================================
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET DEADLOCK_PRIORITY HIGH

DECLARE @Fulltablename NVARCHAR(MAX)= ( SELECT CONCAT(@SchemaName,
‘.’, @TableName)
)
DECLARE @columns AS NVARCHAR(MAX)
DECLARE @PramiryColumns NVARCHAR(1000)
DECLARE @PramiryColumnsSpicial NVARCHAR(1000)
DECLARE @PramiryColumnsCount INT
DECLARE @PramiryColumnsSerial NVARCHAR(1000)
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Data_type AS NVARCHAR(200)
DECLARE @identity INT

CREATE TABLE #PrimaryKey
(
Column_Name NVARCHAR(200)
COLLATE SQL_Latin1_General_CP1256_CI_AS
)
CREATE CLUSTERED INDEX [IX_#PrimaryKey] ON #PrimaryKey
(Column_Name ASC)WITH(FILLFACTOR=80,DATA_COMPRESSION=PAGE)

INSERT INTO #PrimaryKey
( Column_Name
)
SELECT Col.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab ,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.CONSTRAINT_NAME = Tab.CONSTRAINT_NAME
AND Col.TABLE_NAME = Tab.TABLE_NAME
AND CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND Col.TABLE_NAME = @TableName
SELECT @PramiryColumnsCount = ( SELECT COUNT(1)
FROM #PrimaryKey
)
SELECT @PramiryColumnsCount AS PramiryColumnsCount
SELECT *
FROM #PrimaryKey
—
IF @PramiryColumnsCount = 1
BEGIN
—Return Pramiry Column info
SELECT @PramiryColumnsSpicial = ( SELECT CONCAT(‘[‘,
Column_Name, ‘]’)
FROM #PrimaryKey
)
SELECT @PramiryColumns = ( SELECT Column_Name
FROM #PrimaryKey
)

SELECT @identity = c.is_identity ,
@Data_type = t.name
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@Fulltablename)
AND c.name = @PramiryColumns
–not uniqueidentifier + IS idintity = 1
IF @Data_type <> ‘uniqueidentifier’
AND @identity = 1
BEGIN
SELECT @columns = COALESCE(@columns + ‘,’, ”)
+ c.name
FROM sys.objects t
INNER JOIN sys.all_columns c ON t.object_id = c.object_id
WHERE t.type = ‘U’
AND t.name = @TableName
AND c.is_identity = 0
AND c.is_computed = 0
AND c.name NOT IN (
SELECT Column_Name COLLATE SQL_Latin1_General_CP1256_CI_AS
FROM #PrimaryKey )
SET @SQL = ‘WITH cte AS
(
SELECT top 1 1 AS Serial, ‘ + @columns + ‘
FROM ‘ + @Fulltablename + ‘
UNION ALL
— recur
SELECT cte.Serial + 1, ‘ + @columns + ‘
FROM cte
WHERE cte.Serial < ‘ + @batchCount + ‘
)
INSERT INTO ‘ + @Fulltablename + ‘
select ‘ + @columns + ‘ from cte
OPTION (MAXRECURSION 0);
‘
PRINT @SQL
EXEC sp_executesql @SQL
END
–Not uniqueidentifier + is idintity = 0
ELSE
IF @Data_type <> ‘uniqueidentifier’
AND @identity = 0
BEGIN
SELECT @columns = COALESCE(@columns + ‘,’, ”)
+ c.name
FROM sys.objects t
INNER JOIN sys.all_columns c ON t.object_id = c.object_id
WHERE t.type = ‘U’
AND t.name = @TableName
AND c.is_identity = 0
AND c.is_computed = 0
AND c.name NOT IN (
SELECT Column_Name COLLATE SQL_Latin1_General_CP1256_CI_AS
FROM #PrimaryKey )
SET @SQL = ‘
declare @ser as int ;
Select @ser=MAX(‘ + @PramiryColumnsSpicial + ‘)+1 from ‘ + @Fulltablename
+ ‘;
WITH cte AS
(
SELECT top 1 @ser AS ‘ + @PramiryColumnsSpicial + ‘, ‘ + @columns
+ ‘
FROM ‘ + @Fulltablename + ‘
UNION ALL
— recur
SELECT cte.’ + @PramiryColumnsSpicial + ‘ + 1, ‘ + @columns + ‘
FROM cte
WHERE cte.’ + @PramiryColumnsSpicial + ‘ < ‘ + @batchCount + ‘
)
INSERT INTO ‘ + @Fulltablename + ‘
select cte.’ + @PramiryColumnsSpicial + ‘,’ + @columns
+ ‘ from cte
OPTION (MAXRECURSION 0);
‘
PRINT @SQL
EXEC sp_executesql @SQL
END
–Uniqueidentifier
ELSE
IF @Data_type = ‘uniqueidentifier’
BEGIN
SELECT @columns = COALESCE(@columns + ‘,’, ”)
+ c.name
FROM sys.objects t
INNER JOIN sys.all_columns c ON t.object_id = c.object_id
WHERE t.type = ‘U’
AND t.name = @TableName
AND c.is_identity = 0
AND c.is_computed = 0
AND c.name NOT IN (
SELECT Column_Name COLLATE SQL_Latin1_General_CP1256_CI_AS
FROM #PrimaryKey )
SET @SQL = ‘WITH cte AS
(
SELECT top 1 1 AS Serial, ‘
+ @PramiryColumnsSpicial + ‘, ‘ + @columns
+ ‘
FROM ‘ + @Fulltablename + ‘
UNION ALL
— recur
SELECT cte.Serial + 1, ‘ + @PramiryColumnsSpicial
+ ‘,’ + @columns + ‘
FROM cte
WHERE cte.Serial < ‘ + @batchCount + ‘
)
INSERT INTO ‘ + @Fulltablename + ‘
select NEWID(),’ + @columns + ‘ from cte
OPTION (MAXRECURSION 0);
‘
PRINT @SQL
EXEC sp_executesql @SQL
END

END

ELSE
IF @PramiryColumnsCount = 0
BEGIN
SELECT @columns = COALESCE(@columns + ‘,’, ”) + c.name
FROM sys.objects t
INNER JOIN sys.all_columns c ON t.object_id = c.object_id
WHERE t.type = ‘U’
AND t.name = @TableName
AND c.is_identity = 0
AND c.is_computed = 0
SET @SQL = ‘WITH cte AS
(
SELECT top 1 1 AS Serial, ‘ + @columns + ‘
FROM ‘ + @Fulltablename + ‘
UNION ALL
— recur
SELECT cte.Serial + 1, ‘ + @columns + ‘
FROM cte
WHERE cte.Serial < ‘ + @batchCount + ‘
)
INSERT INTO ‘ + @Fulltablename + ‘
select ‘ + @columns + ‘ from cte
OPTION (MAXRECURSION 0);
‘
PRINT @SQL
EXEC sp_executesql @SQL
END

ELSE
BEGIN
PRINT ‘this table ‘ + @Fulltablename
+ ‘ have Composite primary Key and this case out of Support’
END
DROP TABLE #PrimaryKey

END

DEMO PART:

• create Schema  and table test1 on it

Create Schema AC

Go
/****** Object: Table [AC].[TEST4] Script Date: 12/02/2015 11:10:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [AC].[TEST1](
 [KEY] [int] NOT NULL,
 [FirstName] [varchar](100) NULL,
 [LastName] [varchar](100) NULL,
 [BirthDate] [datetime] NULL,
 [BirthMonth] AS (datepart(month,[BirthDate])),
 CONSTRAINT [PK_TEST4] PRIMARY KEY CLUSTERED
(
 [KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80,data_compression=page) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

• insert one record on the table

Insert into [AC].[TEST1]
Values
(1,'Mustafa','EL-Masry',GETDATE())

• Execute Stored procedures FillingTable

Exec [dbo].[FillingTable] 'AC' ,'TEST1','1000000'

Select Count(1) from AC.test1 with (Nolock)

after this you will find the table now have one millions of record

Follow the author:

View all my tips , LinkedIn , Website , Slideshare