-
Title
ApexSQL Generate Command Line Interface (CLI) switches -
Resolution
Applies to
ApexSQL GenerateSummary
This article explains the ApexSQL Generate CLI switches and their usage through the examplesDescription
Connecting options:
/server Specifies server and instance to connect to Alias /s Format: /server:server_name\instance_name,port Note: If server is not specified, local instance is assumed /database Specifies the database to load for generation Alias /d Format: /database:database_name 
Quick tip:
Type * to populate all the databases on the given instance simultaneously
/user Specifies username for SQL Server authentication login Alias /u Format: /username:username Note: If username is not specified, Windows authentication is assumed /password Password specified along with the /user switch Alias /p Format: /password:password Specific options:
/project Specifies project file path previously saved in the application Alias /pf Format: /project:
<Project_file_path\file_name.axgn>/sql_script_source_path Connect to a database by loading the database structure from script Alias /sp Format: /sql_script_source_path:
<Script_file_path\Script_file_name.sql>/rows Specifies the number of rows to be generated for each table Alias /r Format: /rows:number_of_rows (numeric value) /exec_time Specifies the generation time for each table in seconds Alias /et Format: /exec_time:
number_of_seconds (numeric value)/clear Truncate tables before generation Alias /c Format: /clear /dis_ins_trig Disables insert triggers to ensure the desired number of rows generated in each table Alias /dit Format: /dis_ins_trig /dis_del_trig Disables delete triggers to ensure the desired number of rows generated in each table Alias /ddt Format: /dis_del_trig /dis_check_con Disables check constraints to ensure the desired number of rows generated in each table Alias /dcc Format: /dis_check_con /pre_script Select a SQL script that will be executed before data generation Alias /prs Format: /pre_script:
<Script_file_path\file_name.sql>/post_script Select a SQL script that will be executed after data generation Alias /pos Format: /post_script:
<Script_file_path\file_name.sql>Export options:
/output_type Export results to the chosen type format (e.g. SQL, XML, CSV, JSON and Excel) Alias /ot Format: /output_type:[SQL|XML|CSV|JSON|Excel] Note: These arguments cannot be combined /output_type2 Export results to the secondary type format Alias /ot2 Format: /output_type2:[SQL|XML|CSV|JSON|Excel] Note: These arguments cannot be combined /output_folder Specifies the path of the directory for the exported files Alias /of Format: /output_folder:<directory_path> /output_folder2 Specifies the secondary path of the directory for the exported files Alias /of2 Format: /output_folder:<directory_path> /csv_delimiter Defines character used to specify the boundary between columns of data in the exported CSV file Alias /del Format: /csv_delimiter:any_character /csv_include_column_
headersWrites column names separated by delimiter as first row in the exported CSV file Alias /ich Format: /csv_include_column_headers /include_transaction_
handlingThis option allows wrapping the INSERT statements in the exported SQL file into explicit transactions Alias /ith Format: /include_transaction_handling /batch_size This option allows specifying the number of INSERT statements to be wrapped in the single batch in the exported SQL file Alias /bs Format: /batch_size:any_number
Quick tip:
Use the /include_transaction_handling and the /batch_size switches to ensure the highest number of rows inserted in case of any error while executing the exported SQL script
/date_format This option applies the defined date format to values in columns with any of date, time or datetime SQL data types Alias /df Format: /date_format: - [dd-MMM-yyy]
- [dd-mm-yyyy]
- [dd-MMM-yy]
- [dd-mm-yy]
- [m-d-yyyy]
- [dd/MMM/yyyy]
- [dd/mm/yyyy]
- [dd/MMM/yy]
- [dd/mm/yy]
- [m-d-yyyy]
- [dd-MMMM-yyyy]
/set_insert_identity_on This options allows explicit values to be inserted into the identity column of a table Alias /iid Format: /set_insert_identity_on /author_name This option adds the author name to the exported SQL script Alias /an Format: /author_name:name_of_the_author /legal_text This option inserts legal text into the SQL script Alias /lt Format: /legal_text:Copyright_text /comments This option adds comment lines to the output SQL script prior to each object Alias /cmt Format: /cmt /print This option adds Print statements to the output SQL script prior to each object Alias /pr Format: /print /inc_excel_header This option adds a customizable header at the top of the Excel file Alias /ieh Format: /inc_excel_header /as_array Wrap data in square brackets and separate rows with comma character in the exported JSON file Alias /a Format: /as_array Examples
Note: All examples assume that the current directory is the application directory. If not, the full path to ApexSQLGenerate.com must be specified.
Clear all and generate new test data in the database on the local server instance:
ApexSQLGenerate.com /d:AdventureWorks2008 /r:1000 /c /v ApexSQL Generate 2016.02, Copyright (C) 1999-2016 ApexSQL LLC Reading tables from database: AdventureWorks2008 Data generation started for database: "AdventureWorks2008" Executing against database... Deleting data from table Sales.Store Deleting data from table Production.Illustration Deleting data from table Person.PersonPhone Deleting data from table Person.PhoneNumberType Deleting data from table Person.StateProvince Inserting data into table Person.StateProvince Inserting data into table Person.PhoneNumberType Inserting data into table Person.PersonPhone Inserting data into table Production.Illustration Inserting data into table Sales.Store Execution successfully finished Total number of inserted rows: 5000 Execution time: 0 hour(s) 0 minute(s) 4 second(s)
Quick tip:
Use the /v (verbose) switch to get full operation information as a console output
Populate a database on the remote server with default instance, disable triggers, disable Check constraints and execute the post-generation script:
ApexSQLGenerate.com /s:CENTRAL-PC,1433 /d:AdventureWorks2014 /r:1500 /dit /ddt /dcc /pos:”C:\Wrap.sql” ApexSQL Generate 2016.02, Copyright (C) 1999-2016 ApexSQL LLC Reading tables from database: AdventureWorks2014 Data generation started for database: "AdventureWorks2014" Executing against database... Inserting data into table dbo.AWBuildVersion Inserting data into table Production.ProductCategory Inserting data into table Production.Location Inserting data into table Sales.SalesOrderDetail Inserting data into table Sales.SalesOrderHeaderSalesReason Executing post-processing script Wrap.sql Execution successfully finished Total number of inserted rows: 7500 Execution time: 0 hour(s) 0 minute(s) 4 second(s)
Populate a database on the named instance with the SQL authentication by the Time execution, with disabled triggers and Check constraints and an console output redirected to an TXT file: ApexSQLGenerate.com /s:RUNDEK-PC\MSSQLSERVER2014 /u:Rundek /p:32756 /d:test_template /et:240 /dit /ddt /dcc /out:”C:\GenerationLog.txt” /v
Note: The specified “execution time” applies to each table separately, which means that four tables will be populated in total of 960 seconds ApexSQL Generate 2016.02, Copyright (C) 1999-2016 ApexSQL LLC Reading tables from database: test_template Data generation started for database: "test_template" Executing against database... Inserting data into table dbo.allTypes Inserting data into table dbo.Table_1 Inserting data into table dbo.TREGCFT Inserting data into table dbo.User Execution successfully finished Total number of inserted rows: 45330039 Execution time: 0 hour(s) 16 minute(s) 4 second(s)
Quick tip:
Save the console output in an TXT file format using the /out switch
Note: Once the console output is redirected to the external file, it will only be previewed in the disputed file, not the console itself.
Merge test data Insert statements with the database creation script and export as a SQL file with explicit values generated in the identity columns and the transaction handling included:
ApexSQLGenerate.com /sp:”C:\DemoDB.sql” /r:35000 /c /v /ot:SQL /of:”C:\CSVexports” /ith /iid ApexSQL Generate 2016.02, Copyright (C) 1999-2016 ApexSQL LLC Reading tables from SQL script "C:\DemoDB.sql" Data generation started for database: "DefaultDb" Exporting to SQL... Exporting data for table Person.BusinessEntity Exporting data for table Person.ContactType Exporting data for table Person.CountryRegion Exporting data for table Production.Location Exporting data for table Production.ProductCategory Exporting data for table Production.ProductModel Exporting data for table Production.UnitMeasure Exporting data for table Person.Person Exporting data for table Sales.SalesTerritory Exporting data for table Purchasing.Vendor Exporting data for table Production.ProductSubcategory Exporting data for table HumanResources.Employee Exporting data for table Person.BusinessEntityContact Exporting data for table Production.Product Exporting data for table Sales.SalesPerson Exporting data for table Production.ProductCostHistory Exporting data for table Production.ProductInventory Exporting data for table Production.ProductListPriceHistory Exporting data for table Sales.Store Exporting data for table Sales.Customer Finished. Export results are saved to "C:\SQLexports" directory Execution time: 0 hour(s) 1 minute(s) 4 second(s)
Export the test data into all given file formats:
ApexSQLGenerate.com /s:RUNDEK-PC\MSSQLSERVER2014 /d:asgt2.0 /r:12000 /ot:* /of:”C:\asgtExp” /v ApexSQL Generate 2016.02, Copyright (C) 1999-2016 ApexSQL LLC No server was specified so assuming (local) server Reading tables from database: asgt2.0 Data generation started for database: "asgt2.0" Test data will be exported into: live database, SQL static script, XML, CSV, JSON and Excel file format Executing against database... Inserting data into table Department.Management Inserting data into table Department.Marketing Execution successfully finished Total number of inserted rows: 24000 Exporting to SQL... Exporting data for table Department.Management Exporting data for table Department.Marketing Finished. Export results are saved to "C:\asgtExp" directory Exporting to XML... Exporting data for table Department.Management Exporting data for table Department.Marketing Finished. Export results are saved to "C:\asgtExp" directory Exporting to JSON... Exporting data for table Department.Management Exporting data for table Department.Marketing Finished. Export results are saved to "C:\asgtExp" directory Exporting to CSV... Exporting data for table Department.Management Exporting data for table Department.Marketing Finished. Export results are saved to "C:\asgtExp" directory Exporting to Excel... Exporting data for table Department.Management Exporting data for table Department.Marketing Finished. Export results are saved to "C:\asgtExp" directory Execution time: 0 hour(s) 0 minute(s) 15 second(s)
Quick tip:
Use the * mark with the /output_type switch to export in all the given exporting formats
Export the test data into CSV and SQL files and save them in two separate directories. Define the delimiter and include column header in CSV files. Include statements for disabling Insert and Deleted triggers, and Check constraints. Set batch size and header. Set insert identity on, include transaction handling. Add comments and Print statement prior to each object in the exported SQL files.
ApexSQLGenerate.com /s:RUNDEK-PC\MSSQLSERVER2014 /d:AdventureWorks2008 /r:1000 /ot:CSV /of:”C:\CSVexport” /del:- /ich /ot2:SQL /of2:”C:\SQLexport” /ith /bs:25 /dit /ddt /dcc /iid /an:Rundek /lt:”Copyright by Rundek” /cmt /pr
ApexSQL Generate 2016.02, Copyright (C) 1999-2016 ApexSQL LLC Reading tables from database: AdventureWorks2008 Data generation started for database: "AdventureWorks2008" Exporting to CSV... Exporting data for table dbo.AWBuildVersion Exporting data for table Purchasing.ShipMethod Exporting data for table Purchasing.PurchaseOrderHeader Exporting data for table Purchasing.PurchaseOrderDetail Exporting data for table Purchasing.ProductVendor Exporting data for table Production.WorkOrderRouting Exporting data for table Production.WorkOrder Exporting data for table Purchasing.Vendor Exporting data for table Production.UnitMeasure Exporting data for table Production.TransactionHistory Exporting data for table Production.ScrapReason Exporting data for table Production.ProductSubcategory Exporting data for table Production.ProductReview Exporting data for table Production.ProductProductPhoto Exporting data for table Production.ProductPhoto Exporting data for table Production.TransactionHistoryArchive Exporting data for table Production.ProductModelProductDescriptionCulture Finished. Export results are saved to "C:\CSVexport" directory Exporting to SQL... Exporting data for table dbo.AWBuildVersion Exporting data for table Purchasing.ShipMethod Exporting data for table Purchasing.PurchaseOrderHeader Exporting data for table Purchasing.PurchaseOrderDetail Exporting data for table Purchasing.ProductVendor Exporting data for table Production.WorkOrderRouting Exporting data for table Production.WorkOrder Exporting data for table Purchasing.Vendor Exporting data for table Production.UnitMeasure Exporting data for table Production.TransactionHistory Exporting data for table Production.ScrapReason Exporting data for table Production.ProductSubcategory Exporting data for table Production.ProductReview Exporting data for table Production.ProductProductPhoto Exporting data for table Production.ProductPhoto Exporting data for table Production.TransactionHistoryArchive Finished. Export results are saved to "C:\SQLexport" directory Execution time: 0 hour(s) 0 minute(s) 2 second(s)