ApexSQL Generate Command Line Interface (CLI) switches

Applies to
ApexSQL Generate

Summary
This article explains the ApexSQL Generate CLI switches and their usage through the examples

Description

Connecting options:

/serverSpecifies 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
/databaseSpecifies the database to load for generation
Alias /d
Format: /database:database_name
Quick tip icon

Quick tip:

Type * to populate all the databases on the given instance simultaneously

/userSpecifies username for SQL Server authentication login
Alias /u
Format: /username:username
Note: If username is not specified, Windows authentication is assumed
/passwordPassword specified along with the /user switch
Alias /p
Format: /password:password

Specific options:

/projectSpecifies project file path previously saved in the application
Alias /pf
Format: /project:
<Project_file_path\file_name.axgn>

/sql_script_source_pathConnect to a database by loading the database structure from script
Alias /sp
Format: /sql_script_source_path:
<Script_file_path\Script_file_name.sql>
/rowsSpecifies the number of rows to be generated for each table
Alias /r
Format: /rows:number_of_rows (numeric value)
/exec_timeSpecifies the generation time for each table in seconds
Alias /et
Format: /exec_time:
number_of_seconds (numeric value)

/clearTruncate tables before generation
Alias /c
Format: /clear
/dis_ins_trigDisables insert triggers to ensure the desired number of rows generated in each table
Alias /dit
Format: /dis_ins_trig
/dis_del_trigDisables delete triggers to ensure the desired number of rows generated in each table
Alias /ddt
Format: /dis_del_trig
/dis_check_conDisables check constraints to ensure the desired number of rows generated in each table
Alias /dcc
Format: /dis_check_con
/pre_scriptSelect a SQL script that will be executed before data generation
Alias /prs
Format: /pre_script:
<Script_file_path\file_name.sql>

/post_scriptSelect a SQL script that will be executed after data generation
Alias /pos
Format: /post_script:
<Script_file_path\file_name.sql>

Export options:

/output_typeExport 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_type2Export results to the secondary type format
Alias /ot2
Format: /output_type2:[SQL|XML|CSV|JSON|Excel]
Note: These arguments cannot be combined

/output_folderSpecifies the path of the directory for the exported files
Alias /of
Format: /output_folder:<directory_path>
/output_folder2Specifies the secondary path of the directory for the exported files
Alias /of2
Format: /output_folder:<directory_path>
/csv_delimiterDefines 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_
headers
Writes column names separated by delimiter as first row in the exported CSV file
Alias /ich
Format: /csv_include_column_headers
/include_transaction_
handling
This option allows wrapping the INSERT statements in the exported SQL file into explicit transactions
Alias /ith
Format: /include_transaction_handling
/batch_sizeThis 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 icon

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_formatThis 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_onThis options allows explicit values to be inserted into the identity column of a table
Alias /iid
Format: /set_insert_identity_on
/author_nameThis option adds the author name to the exported SQL script
Alias /an
Format: /author_name:name_of_the_author
/legal_textThis option inserts legal text into the SQL script
Alias /lt
Format: /legal_text:Copyright_text
/commentsThis option adds comment lines to the output SQL script prior to each object
Alias /cmt
Format: /cmt
/printThis option adds Print statements to the output SQL script prior to each object
Alias /pr
Format: /print
/inc_excel_headerThis option adds a customizable header at the top of the Excel file
Alias /ieh
Format: /inc_excel_header
/as_arrayWrap 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 icon

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 icon

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 icon

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)