Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

SQL Data Generator

In this article, we can take a general idea of how we can prepare SQL Server Data Generator to produce information.

Users are recommended to make back up the database that they are going to populate before they create the information. After that they can regulate the settings and do again the information production if they are not pleased by means of the outcome.

To produce information, primarily generate an assignment by selecting the SQL Server and database you want to settle. The plan also defines a number of options for the information era collection, and they can identify any amount of SQL scripts that they want SQL Generator to execute robotically earlier than or behind generating the information. When they have shaped a plan, the diagram of the database they have chosen is scheduled in a tree view in the Tuples to populate sheet.

Populate Tuples:

They identify the tuples that you want to populate. By default, these are all chosen, but they can alter this choice for both new projects and new tuples in your request options. To see the creation of SQL script for a tuple, right-click the table or column name in the tree view and click demonstrate Schema Creation Script.

When you have selected to populate a tuple, they can describe how they would like the information to be generated: click the table name in the Tables to populate pane, and specify the details in the Table generation settings pane.

They can choose to:

  • create data using generators:
  • SQL Data Generator robotically assigns a generator to each column based on its tuple name, column name, information type, and span. If the column has conditions, SQL information producer practices these, to organize the producer arguments for the column; if the conditions cannot be complied with this way, they can modify the producer used by a particular article later if required.

  • import data:
  • They can bring in a tuple or view as a running database, or a running comma separated value file. SQL information producer draws the columns based on name and information type. If any columns cannot be diagrammed, SQL information producer assigns a generator. They can alter the design later if necessary. For comprehensive in sequence.

Note that:

  • The foreign keys are robotically assigned the Foreign Key producer; this cannot be altered to a different producer, but they can modify its settings
  • Columns for which information is automatic exhibit Server Assigned in the Generator pack; this cannot be altered.

For instance, uniqueness of columns and computed columns are server assigned. In the tuple making configurations, we can also specify the number of rows that you want to be generated, and whether you want existing data to be deleted prior to populating the table. We can see pre printing state of the information that will be produced for each table in the preprinting pane.

Warnings and errors:

At what time they are setting up the tuples and columns that you want to populate, SQL information producer shows warning and error post to notify them while near may be a trouble with the information production. Errors put off them on or after electing Generate Data. For instance, an error is showed if there are spherical dependencies.

Warnings notify them as a trouble may happen all through information production. The troubles do not put off them from making information, other than it might discontinue the information production for a meticulous tuple. For instance, a caution is showed if they have selected to delete rows in a tuple, but one more tuple references it. Information messages provide you with information about issues that you may want to correct, other than which will not stop information production. For example, you may see an information message if SQL Data Generator cannot create enough rows for the preview.

To observe the particulars of an error or warning, open your pointer over the picture to explain a tooltip, or click the column name to observe the particulars in the Column production setting pane.

Run Project dialog:

Selecting a mission as of the plan catalog and by means of the red Execute button opens the Run Project dialog. The Advanced information producer also supports organization many projects, one subsequent to an additional, if you choose many substance from the list. As well, you can prefer to run a plan on numerous databases by with the Run Project on All Databases button in the project list. In Run Project dialog, just after using the Run Project button if you prefer not to let alone the present run, you can verify the databases to run the project for and click the Start Process button in the dialog.

The dialog is alienated in two parts, the substance to produce data for part (on the left) and Current Item part (on the right). Once happening, the Run button becomes busy and the red Cancel Process button is enabled. You can abandon the information production procedure at a few occasions via the Cancel Process push button. Any un-dedicated deal will be dedicated when you do this. At the left, you can observe the tuples and additional substance in this scheme and the order they're in. Each of this substance will have generate for and the particulars for the present thing are individual showed on the correct. Once an thing has been ended, a check mark is drawn next to it.

Save log to database:

The huge memorandum be in charge of the swift log and shows the development as text post and any errors that happen. After the method has completed, you can optionally save the log into the higher Data Generator folder via the Save Log to Database button. I freshly required generating thousands of test assembly proceedings for our venture VPN management item for consumption. I've complete this additional or fewer physically in the precedent and establish that it's almost not possible to make sensible ISP and VPN assembly information lacking spend a pair existence writing and alteration SQL scripts. This instance I determined to investigate the snare (again) intended for some item for consumption to execute this tricky and obscure task.

Generating Dummy Data for Testing:

I have performed on an amount of jobs where there hasn’t been sufficient information to test information and extracts and additional database applications. The database was an external database and the retailer wouldn’t permit information to be put in by the use of the back end. We didn’t encompass sufficient information to check retort era or to organize SQL until after Online. This caused a group of redraft after Online which in twist exaggerated the awareness of quality of the scheme as a whole. The reason of this piece is to give resources of generating haphazard facts which tin can be entered into a database for checking purposes. The scripts and technology permit example information to be produced. The scripts underneath create a amount of tuples for reserving unlike information descriptors, for instance name, street type, number, transactions. Some tuples are reused to produce street names and post codes. If we required information so as to be merely based on information type and had no other connotation you might categorize the tuples by information kind for instance date time, character and numeric. Information is produced by making Cartesian joins on the tuples. Using the produced dataset this gives 7,000,000 (10 x 10 x 1000 x 3.5 x 20 x 1) rows of data. Id dates are produced by means of a series.

Top of Form

Generating Dummy Data:

I have performed on a amount of missions anywhere present hasn’t been sufficient information to test information and extracts and additional database applications. The database was external database and the vendor wouldn’t let information to be entered via the back end. We didn’t have sufficient information to investigation response times or to organize SQL until after online. This caused a collection of say differently after online which in turn pretentious the insight of excellence of the scheme as a entire.

The cause of this piece of writing is to give a means of generating random information which can be entered into a database for testing purposes.


The result is figured out into a numeral of layers. They are:

  • The foundation Data Layer
  • The coverage Data Layer

These layers can be inside unusual diagrams inside the database.

Foundation Data Layer:

The Foundation data layer contains the model information tuples, any information production scripts, and a progression or sequences for generating Ids.

Coverage Data Layer:

This layer contains derivative tuples and views which generate facts like to what would be displayed in the submission that we are checking. There may be a number of steps involved in getting data from the base tables into a format that can be imported into your application database.

Import the data into all user databases:

You can use the SQL Server bulk copy value (BCP) to introduce the information in each tuple from the source. The source code can do the uncomplicated by SMO and BCP jointly:

Listing 1: Sample showing execution plan

private void btnImprt_click(object sender, EventArgs e)
                DialogResult result = openFileDialog1.ShowDialog();
                if (result == DialogResult.OK)
                    string Impfile = openFileDialog1.FileName;
                    if (Impfile == "*.xls")
                        excelConectionStrng = String.Format("Provider=Microsoft.Jet.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;Importmixedtypes=text;typeguessrows=0;\"", Impfile);
                        excelConectionStrng = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;Importmixedtypes=text;typeguessrows=0;\"", Impfile);
                    using (OleDbConnection conection = new OleDbConnection(excelConectionStrng))
                        OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", conection);

                        using (DbDataReader dr = command.ExecuteReader())
                            string sqlConectionStrng = @"Persist Security Info=False;User ID=sa;Password=A1d4m1N;Initial Catalog=ICTPayroll;Data Source=SERVER";

                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConectionStrng))
                                bulkCopy.DestinationTableName = "EmpMst";                                
            catch (Exception ex)


The information production steps permits you to rapidly arrange an information production diagram so as to pull all rows on or after an obtainable SQL Server database. The steps will only permit us to drag information on or after a SQL Server database, since the plan of the matter inside the database has to equal the tuple in the database program. Hope you have understood the data generator concepts. Keep reading !!

Website: Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login