Welcome to Freedom Technologies, In this post, we’ll discuss inserting large amounts of random data into SQL tables for performance testing in the next post we will use these large tables to test the performance of queries that use Joins and subqueries.
How populate sql table with random data?
There are three parts to the SQL script.
The first part will actually check if the tables exist, TblStudent and TblDonation
If the tables exist, they will be dropped. And then the second part, we recreate those tables and the final part will insert random sample data into those tables. Let’s see how we are checking for the existence of those tables and dropping them.
So to check the existence of tables, I’m using the system table called information_schema.tables. Let’s see what this table contains. So let’s execute query, select * from information_schema.tables
And look at this. This query has returned me with a list of table names. Where are these tables present? These tables are present in the TestDatabase because we executed a query within the context of the TestDatabase database.
I got all the tables that are present within that TestDatabase database and we are checking here if the table name is TblStudent. So do we have TblStudent within information_schema.tables? Yes, and we are passing that to Exists() function.
Exist() function will return true if the table exists, otherwise, it’s going to return false. So if this function returns true, that means the table is present. We want to drop it. So drop table TblStudent.
then the second part will actually recreate those tables, create table TblStudent and TblDonation table. and the important thing to notice here is that StudentId is a foreign key. So this is referring to the ID column in the TblStudent table.
So the third and final step is to actually insert sample data, inserting sample data into tables. It’s very simple and straightforward.
So if you look at the TblStudent table, it has got three columns within that id, StudentName , FatherName Id column is the identity column.Meaning when we insert a row into table TblStudent, we don’t have to supply a value for the identity column.
We only need to supply values for name and description columns which are of type and [nvarchar]. so the name is going to be dynamically computed. So the name of the student, since this random data I’m going to call my student says student1,student2,student3 …Studentn, etc.. Similarly, the father’s name description is going to be Father Name 1, Father Name 2, etc.
So while we declare a variable of type integer @Id and the @Id is initialized to 1, so while that @Id is less than or equal to 200000, which means we are going to loop through 200000 times, I’m going to insert 200000 students into the TblStudent table. So what we are doing insert into TblStudent values. So first we need to supply the value for the name column and then for the first name.
And to that what we are doing, we are concatenating the @Id value first-time @Id value will be 1. We are converting that to nvarchar because @Id is an integer variable. But we are since we are concatenating that to another string, we need to typecast that to be of type nvarchar, which is what we are doing here.
So that will give value for the name column, Student Name-1, Student Name-2, etc.
And ‘Father Name – ‘ + CAST(@Id as nvarchar(20)) will give me Father Name -1, which is going to be the value for the Father Name column.
Now you execute the above query it will take approx 2-3 minutes.
The post How to generate random data in sql server for performance testing appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Left outer join with subqueries with an inner join
- SQL sum or subtracted based on a column (transaction type)
- Comparing two select statements
- What is the most efficient way to deploy a WPF application together with a SQL Server database?
- Best way to compare the end of a string, use RIGHT, LIKE or other?
- Maintain ordering of characters if there is no id (SQL Server 2005)
- Bulk insert strategy from c# to SQL Server
- SQL Server 2014 CTP1 Setup failed
- Write large R dataframe to SQL Server database
- Can you call a SQL Stored Procedure that returns a record set and have those values loaded into variables?
- Sum of cash and cheque from a table
- sql server 2012 merge values from two tables
- Fill JS DataTable with conditions - Entity Framework MVC5
- META DATA in Database backup-Sql server 2008
- How to move Azure SQL Database files to different location?
- how can I speed up insertion of many rows to a table via ADO.NET?
- SQL, parse string like "12M" to 12 * 30 as an int
- Customizing placement of report parameters
- NOT EXISTS vs NOT IN
- Return list of all nodes from xml column in sql database
- Difference between SQL Database and SQL Server in Microsoft Azure Portal
- OleDb - Incorrect syntax near the keyword 'DEFAULT'
- Django with MSSQL giving error :'DatabaseWrapper' object has no attribute 'Database'
- Giving different users different Permissions on database
- SQL Server - remote Oracle query extremely slow on date clause
- SQL Joins stop working when table columns are defined
- order by case and alias
- How do you maintain large t-sql procedures
- Error when creating DB with a Filestream
- Is it possible to to retrieve a top ROW_NUMBER() for each group without a sub-query?
- To get digits after / in stored procedure
- Making a foreign key reference only a subset of rows in the referenced table
- How to give latest database name in Shrink database sql script?
- IOS app , searchbox and SQL database
- What is syncobj in SQL Server
- Turbogears 2 Tutorials?
- How Do I Update A Table From A Different SQL Server
- Getting current system time in SQL Server
- Query so much slower than stored procedure?
- Combining repeating row cells
- Two counts in one query with opposing where criteria
- Retrieving and using the output from a "FOR JSON" query in a variable
- Combining Multiple results without duplication
- Assign query result into a session
- Query SQL Server with IN (NULL) not working
- Set below query into asc or desc order?
- Merging an existing object does not update the changed @Embedded in the database
- Capturing Top 100 Costly queries in SQL Server (<2014)
- about sql server query
- How to store multiple database columns into an array with Linq2Sql
- Get Long Running Queries For Users Or Applications Queries
- How to calculate difference between 2 times in SQL
- SQL Server Tables Relationship in Access
- Insert values and default on all others columns
- In SQL Server 2005 is there any way to change the collation of a whole database at once?
- select max value in each 5 rows
- When should you use full-text indexing?
- User Validation using JSP
- ConnectionString for SQL Server
- What's the purpose of FOREIGN KEY (Key_Name) REFERENCES null (null);
- Does inner join order and where as an impact on performance
- SQL Server : create a trigger on update of "current" table to "history" table
- SQL Server 2012 - Convert a From-To columned table into a numbered sequence
- At what cardinality does SQL Server switch to an index scan (vs. seek)
- SQL Server query to group sequential date data
- SQL query with join, sum, group by, etc
- SQL Server: permissions to read database diagrams
- ODBC SQL type -155 is not yet supported
- SSMS data import and export not maintaining view structure
- How can I add a dynamic number of columns to my query?
- Extracting messages from SQL Server Service Broker
- If condition in SQLCMD script in sql database project
- SQL: Sum(colA-colB) vs Sum(colA)-sum(colB)
- Insert piped SQL data into XML
- Window functions in SQL Server
- How to perform read data from file, then validate data and insert data in to Database operations asynchronously
- SQL> Selecting Multiple Sums Given Unique Conditions from Diff. Tables
- SQL Server - check input parameter for null or zero
- performance/space sql: select into a @variable or into a #temp
- CREATE ASSEMBLY error SQL Server 2008 R2
- Automate Regression Analysis in SQL Database
- Trouble with tsql not in query
- Unable to Reverse Engineer (DBRE) in Spring Roo
- How to build SSIS package to UPSERT/DELETE data from Salesforce to SQL Server database?
- Using CASE Statement in Stored Procedure
- Transpose rows of string values to one column in a dataflow with SSIS
- Unable to access database on different subnet from IIS application
- SQL Server Date Logic - Finding the next reminder date
- SQL Server index included columns
- SQL Server Simple Redundancy
- Create a recursive list of dates given from two Different Dates and items
- Merging two tables
- Check if record exists before adding it to the database
- Hearts change to question mark when I insert data into the database. How do I change the charset of the table?
- Categories all dates by month in SQL Server with set-based approach
- Msg 6522 "Cannot load dynamically generated serialization assembly" doing XML Serialization
- Returning rows where 1 column is the same and other column is different
- Access result from SELECT statement in stored procedure using EF Core and ExecuteSqlCommandAsync
- Calling stored procedure Error: Error converting data type nvarchar to datetime
- How to use IF statement inside WITH?
- MSSQL / MySQL Support for AWS SimpleDB
- SQL Date Subtraction Between Two Years
- Sum records based in two variables
- creating a procedure that accepts a path for JSON file
- Shredding XML with unknown schema
- How to use condition in T-SQL
- Is there a way to create a "pivot group" of columns with t-sql?
- How do I index foreign keys using Fluent NHibernate and SchemaUpdate.Execute()?
- Why am I getting "Unable to load the specified metadata resource" error?
- SQL INNER JOIN with DISTINCT rows?
- Output parameter from SQL Server stored procedure truncated at 4000 characters
- SQL Server Compact 'Data Directory' macro in Connection String - more info needed
- Why use INCLUDE in a SQL index
- Count of rows affected by SELECT INTO query with Microsoft JDBC driver
- UPDATE Table with CTE update always with the same value
- How do I get the char index of an exact word in SQL Server
- How to insert into a table with no writable columns in SQL Server 2005?
- NodeJS: How to connect to MSQL database using Windows Authentication/trustedConnection from Mac
- Do not select an ID if any status = Pending
- Non-clustered GUID primary key OR clustered int primary key and GUID secondary key with unique index?
- Entity Framework with Multiple DB Engines
- Displaying multiple rows from database to WPF app
- Select Times in a day by Pivot in SQL
- SQL query to fetch the data from two tables with "not in" condition
- How can I update strings within an SQL server table based on a query?
- How to repeat the row values
- How to select product that have the maximum price of each category?
- SQL table filter on multiple columns
- In T-SQL how can I get a column of 2-decimal points(or percentages)?
- save image in mssql database as varbinary(max) without conversion
- Why do two columns gets updated when I specified only one to be updated? and how to correct it?
- select a row based on a particular condition
- Combining multiple column into one in sql server 2005
- PostgreSQL and/or SQL Server alternatives to Oracle's SYS_CONTEXT
- SQL server CASE error appear
- how use SQL WHERE CASE with NOT IN or equals at the same time?
- Declare a local variable in named calculation expression
- Thinking behind decision of database connection pool size
- Remove rows whose sum is equal to 0
- How to dump the script from sql server?
- Powershell - The term 'New-SqlColumnEncryptionKey' is not recognized - trying to create Column Encryption Key
- Selecting the values of existing column as values of new columns with one row shift
- Calculate duration from Times stored as Char(4)
- Razor C# - Get data from database
- Using OpenQuery With INSERT INTO Statement
- Execute WITH statement inside a function
- Does SqlCommand.ExecuteNonQuery waits till completion of stored procedure?
- Distinct query not working fine in sqlserver 2005
- Deleted Database Still Shows in Object Explorer
- Pivot data in SQL Server