Bulk Update with SQL
Verb: sqlBulkUpdate
Available from: <Enterprise>
Updates an SQL Database table with data mapped to a data table as long as the identifier of this data table is the same as the table identifier in the Database.
Syntax
sqlBulkUpdate --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 compare with the SQL Database table. |
| --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 existing SQL table where data must be updated. |
| --batchsize | Batch Size | Optional | Number | Number of table rows updated 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 wait time for command execution. |
Outputs
| Script | Designer | AcceptedTypes | Description |
|---|---|---|---|
| value | Success | Boolean | Returns "True" if the Database table was successfully updated, or "False" if it was not. |
Example
Updates a Database table with data mapped to an Excel table, where the identifier is the same as the table's primary key.
defVar --name sqliteConnection --type DbConnection
defVar --name booksTable --type DataTable
defVar --name bookContentTableFile --type DataTable
defVar --name excelFileBooks --type Excel
defVar --name updateResult --type Boolean
// Download the following file to execute the command.
// Connect to the DB.
sqliteConnect --connectionString "Data Source=bdTechnologyUpdate.db;Version=3;UseUTF16Encoding=True;" sqliteConnection=connection
// Execute a query with SELECT to display the book table.
sqlExecuteReader --connection ${sqliteConnection} --statement "SELECT * FROM books;" booksTable=value
logMessage --message "${booksTable}" --type "Info"
// Book table contents before update:
// 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.
// Open the Excel file.
excelOpen --file "excelTableDataBooksUpdate.xlsx" excelFileBooks=value
// Get the table in Excel.
excelGetTable --file ${excelFileBooks} --getfirstsheet --entiretable --hasheaders bookContentTableFile=value
// Updates publisher names and number of pages.
sqlBulkUpdate --dataTable ${bookContentTableFile} --connection ${sqliteConnection} --tablename books --mappings "number=4=publisher,number=5=numPage,number=1=idBook" updateResult=value
sqlExecuteReader --connection ${sqliteConnection} --statement "SELECT * FROM books" booksTable=value
logMessage --message "${booksTable}" --type "Info"
// Book table contents after update:
// 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 File - bdTechnologyUpdate.db
For the correct operation of the above script, it is necessary to download the files, insert the path of the first one in the Connection String parameter of the SQLite Connection command, and the second path in the File parameter of the Open Excel File command.