Bulk Insert with SQL
Verb: sqlBulkInsert
Available from: <Enterprise>
Inserts data from a table into an SQL Database if its identifiers have the same value as the given data table.
Syntax
sqlBulkInsert --dataTable(DataTable) --connection(DbConnection) --tablename(String) [--batchsize(Numeric)] [--mappings(String)] [--timeout(TimeSpan)] (Boolean)=value
Inputs
| Script | Designer | Required | AcceptedTypes | Description |
|---|---|---|---|---|
| --dataTable | Data Table | Required | Data Table | Data table to be compared. |
| --connection | Connection | Required | Database Connection | Database connection variable. You must have a Database connection with a valid connection string. Options:
|
| --tablename | Table Name | Required | Text | Name of the SQL table where data should be inserted into. |
| --batchsize | Rows Quantity | Optional | Number | Number of table rows inserted at a time.
A number greater than or equal to 1 must be entered. |
| --mappings | Mappings | Optional | Text | Mappings of the given Data Table columns using the SQL table values. When adding new mappings you will find the following fields: - Column name: The name of the Data Table column you want to map; - Column number:
The number of the Data Table column you want to map. Column numbers start at 1; - Unnamed field: Refers to the SQL table column name.
A value must be entered in one of the Column name or Column number fields only. |
| --timeout | Timeout | Optional | Time Span, Number, Text | Maximum waiting time for command execution. |
Outputs
| Script | Designer | AcceptedTypes | Description |
|---|---|---|---|
| value | Success | Boolean | Returns "True" if data in the Database table is successfully entered, or "False" if an error occurs. |
Example
The command inserts data from an Excel table into the SQLite Database for each database identifier that is the same as the Excel spreadsheet. The table in the Database with the entered items is displayed.
defVar --name sqliteConnection --type DbConnection
defVar --name booksTable --type DataTable
defVar --name booksTableData --type DataTable
defVar --name excelBooksDataFile --type Excel
defVar --name updateResult --type Boolean
defVar --name insertResult --type Boolean
// Download the following file to execute the command.
sqliteConnect --connectionString "Data Source=bdTechnologyInsert.db;Version=3;UseUTF16Encoding=True;" sqliteConnection=connection
sqlExecuteReader --connection ${sqliteConnection} --statement "SELECT * FROM books;" booksTable=value
logMessage --message "${booksTable}" --type "Info"
// Database Contents before inserting items:
// 1, The Mater Algorithm: How the Search for the Ultimate Machine Learnig Algorithm Recreated Our Universe, Pedro Domingos, Novatec, 344
// 2, Steve Jobs, Walter Isaacson, Companhia das Letras, 624
// 3, Elon Musk: How spacex and tesla's billionarie CEO is shaping our world, Ashlee Vance, Intrínseca, 416
// 4, Brief answers to big questions, Stephen Hawking, Intrínseca, 256
// Download the following file to execute the command.
excelOpen --file "excelTableDataBooksInsert.xlsx" excelBooksDataFile=value
excelGetTable --file ${excelBooksDataFile} --getfirstsheet --entiretable --hasheaders booksTableData=value
sqlBulkInsert --dataTable ${booksTableData} --connection ${sqliteConnection} --tablename books --mappings "number=1=idBook,number=2=name,number=3=author,number=4=publisher,number=5=numPage" insertResult=value
sqlExecuteReader --connection ${sqliteConnection} --statement "SELECT * FROM books" booksTable=value
logMessage --message "${booksTable}" --type "Info"
// Contents after inserting items:
// 1, The Mater Algorithm: How the Search for the Ultimate Machine Learnig Algorithm Recreated Our Universe, Pedro Domingos, Novatec, 344
// 2, Steve Jobs, Walter Isaacson, Companhia das Letras, 624
// 3, Elon Musk: How spacex and tesla's billionarie CEO is shaping our world, Ashlee Vance, Intrínseca, 416
// 4, Brief answers to big questions, Stephen Hawking, Intrínseca, 256
// 5, Artificial Intelligence, Peter Norvig, GEN LTC, 1016
// 6, Astrophysics for those in a hurry, Neil Degrasse Tyson, Planeta, 192
Download File - bdTechnologyInsert.db