Update SQL Wizard

SQL Wizard

SQL statements can be easily created with the Wizard .

For DataSpider Studio for Web, see here.

Start a SQL Wizard (Updating the SQL statement)

To start a SQL wizard, enter [SQL wizard] in the properties settings dialog in the Execute SQL Wizard (Updating SQL Statement) in the database adapter.

Screen structure of the SQL Wizard (Updating the SQL statement)

Data Update settings (Insert)

Numbering in the figure	Name	Description	Remarks
(1)	DML	Specifies DML. [INSERT]:(default) Insert data. [UPDATE]: Update data. [DELETE]: Delete data.
(2)	Table list view	Display a list of tables.	The maximum number of tables listed is determined by Global Resource settings. To add a table, select [Add] in the right-click menu in the root element and specify the table name.
(3)	Table canvas	Configure the definition of inserting a write table.	To delete a write table, select [Remove] from the right-click menu in the table.
(4)	Insert column editor	Set the insert value of the write column.	Please use the consistent format for each database.
(5)	Root elements	Display a database name of the connecting destination.	You can add tables which are not displayed in the table list view by clicking [Add] in the right-click menu in Root Elements.
(6)	Write Table	Tables as writing destinations. Configure which column to set as a writing destination.	The columns checked will be added to the insert column editor.
(7)	Help	Start Help and display the SQL Wizard (Updating SQL Statement) page.

Data update setting screen (Update)

Numbering in the figure	Name	Description	Remarks
(1)	Table list view	Display a list of tables.	The maximum number of tables listed is determined by Global Resource settings. To add a table, select [Add] in the right-click menu in the root element and specify the table name.
(2)	Table canvas	Configure the definition of inserting a write table.	To delete a write table, select [Remove] in the right-click menu of the table.
(3)	Update Column Editor	Set the update value of a write column.	Please use the consistent format for each database.
(4)	Condition Column Editor	Set conditions of a write column.	Please use a consistent format for each database.
(5)	Write Table	Tables as writing destination. Configure which column to set as writing destination.	The columns checked will be added to the insert column editor.
(6)	Add to condition	Add the column selected in the write table to the condition column.
(7)	and/or	Logic operators for multiple conditions. [AND] [OR]

Data update setting screen (Delete)

Numbering in the figure	Name	Description	Remarks
(1)	Table list view	Displays a list of tables.	The maximum number of tables listed is determined by Global Resource settings. To add a table, select [Add] in the right-click menu in the root element and specify the table name.
(2)	Table canvas	Configure the definition of inserting to a write table.	To delete the write table, select [Remove] in the right-click menu of the table.
(3)	Condition column editor	Set the update value of a write column.	Please use the consistent format for each database.
(4)	Write Table	Tables as writing destination. Configure which column to set as writing destination.	The columns checked will be added to the insert column editor.
(5)	Add to condition	Add the column selected in the write table to the condition column.
(6)	and/or	Logic operators for multiple conditions. [AND] [OR]
(7)	Up	Move up the write column.
(8)	Down	Move down the write column.
(9)	Remove	Delete s write column from the condition column.

The contents of the SQL Statement and the Test Execution Screen

Numbering in the figure	Name	Description	Remarks
(1)	SQL statement tab	Displays the automatically generated SQL statement.
(2)	Execution result tab	Displays the result of the test execution.
(3)	Error tab	If an error occurs during test execution, the error is displayed.
(4)	SQL statement editor	It is possible to edit the automatically generated SQL Statement. Also possible to write SQL functions which cannot be generated in the SQL Wizard.	If a SQL Statement is manually edited, it is not possible to edit in the SQL Wizard.
(5)	Execution test	Execute the SQL statement.

Generate a SQL statement

To generate a SQL Statement, follow the steps below.

INSERT
UPDATE
DELETE

INSERT

Select [INSERT] from the [DML] list.
If already selected, no need to reselect DML.

From the table list view, drag & drop the writing destination table onto the table canvas.

In the write table, select the writing destination column from the checkbox.

Set the insert value in the insert column editor.

To generate a SQL statement with the above settings, click [Next].

A SQL statement generated in the SQL statement editor in the [SQL statement] tab is displayed.
Click [Submit] to save the SQL statement and end the SQL wizard.

To execute the SQL statement, click [Execution test].

The results are shown in the [Execution result] tab.

If an error occurs during test execution, the error is displayed in the [Error] tab.

Click [Submit] to save the SQL statement and end the SQL wizard.

To edit the SQL statement, click [SQL wizard] in the properties settings dialog in the Execute SQL (Specifying SQL Statement).
If the SQL Statement is manually edited, it is not possible to edit in the SQL Wizard.

UPDATE

Select [UPDATE] from the [DML] list.

From the table list view, drag & drop the writing destination table onto the table canvas.

In the write table, select the writing destination column from the checkbox.

Set the insert value in the insert column editor.

Set the upload value in the upload column editor.

To specify the condition, select the column you want to use in the write table and click [Add to condition].
The value for the condition column is specified in the condition column editor.
If conditions for multiple columns are required, set logic operators (AND/OR).(Select from [AND] and [OR] from the [and/or] column list.)

To generate a SQL statement with the above settings, click [Next].

A SQL statement generated in the SQL statement editor in the [SQL statement] tab is displayed.
Click [Submit] to save the SQL statement and end the SQL wizard.

To execute a SQL statement, click [Execution test].

The results are shown in the [Execution result] tab.

If an error occurs during test execution, the error is displayed in the [Error] tab.

Click [Submit] to save the SQL statement and end the SQL wizard.

To edit a SQL statement, click [SQL wizard] in the properties settings dialog in the Execute SQL (Specifying SQL Statement).
If the SQL Statement is manually edited, it is not possible to edit in the SQL Wizard.

DELETE

Select [DELETE] from the [DML] list.

From the table list view, drag & drop the writing destination table onto the table canvas.

To specify the condition, select the column you want to use for a condition in the write table and click [Add to condition].
The value for the condition column is specified in the condition column editor.
If conditions for multiple columns are required, set logic operators (AND/OR). (Select from [AND] and [OR] from the [and/or] column list. )

To generate a SQL statement with the above settings, click [Next].

A SQL statement generated in the SQL statement editor in the [SQL statement] tab is displayed.
Click [Submit] to save the SQL statement and end the SQL wizard.

To execute the SQL statement, click [Execution test].

The results are shown in the [Execution result] tab.

If an error occurs during test execution, the error is displayed in the [Error] tab.

Click [Submit] to save the SQL statement and end the SQL wizard.

To edit a SQL statement, click [SQL Wizard] in the properties settings dialog in Execute SQL (Specifying SQL Statement).
If a SQL Statement is manually edited, it is not possible to edit in the SQL Wizard.

Specification Limits

For XA resource, test cannot be executed.

Schema name and table name in database "."Tables containing the selected tables and drag it to the panel can not be dropped.

SQL statements cannot be edited if:
- The tables in the SQL statement do not exist.
- Environmental variables are used.
In the global resource of DB2 for i adapter and DB2 UDB for iSeries adapter, when [Perform auto-commit] is checked, rollback is not performed in test execution, and the data is updated.

In the global resource of JDBC adapter, when [Execute transaction processing] is not checked, rollback is not performed in test execution, and the data is updated.