Applies to
ApexSQL Generate
Summary
This article explains the ApexSQL Generate CLI switches and their usage through the examples
Description
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_ 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_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:
|
| /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 |
| 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)
