Two methods
...
and how to choose method to use
show examples of both
split into multiple pages (execute and merge)
conn:execute
To write to a database you can use conn:execute{}. This allows you to execute any ad hoc SQL statement on a database, including UPDATE and INSERT statements. Can also call stored procedures.
By default, SQL statements specified in calls to conn:execute{} are not executed while you are editing the script. They are only executed when the channel is being run. This behavior can be overridden by setting the live parameter to true, in which case the statements are executed every time the script is executed in the editor. Be cautious about using live=true since the script is executed many times to get the annotation and auto-completion data (which can cause many INSERTs and UPDATEs) .
db.tables and conn:merge
what are
db.tablesandconn:merge. how
...
show examples of both
...
are VDBs used.
A call to
conn:merge{}will either insert new rows in a database table or update existing rows. This depends on how the VDB was configured, see Key Fields and Examples.If no key columns are defined, new rows are always inserted. Some applications prefer this behavior, since data is never overwritten.
If key columns are defined, Iguana performs a SELECT statement to attempt to find rows that match the values provided in the key columns. If a row exists, an UPDATE operation is performed. Otherwise, an INSERT is performed.
separate page on bulk inserts?
Bulk insert is an optional
bulk_insertboolean flag forconn:merge{}that can be used with Microsoft SQL Server and MySQL. It ignores the key columns defined in the VMD files and always does an INSERT using the bulk insert syntax. If your table has a large number of rows, this can improve performance.Note: Bulk insert does inserts only, if you need to do updates you must handle these separately. You will get an error if you try to merge a duplicate record.
db:merge does not support spaces in column names