Merge Table

AQT has a dialog for building and running a Merge Table statement.

You open this dialog from the Database Explorer window by either:

• selecting a table and clicking Table > Merge Table
• selecting a table and right-click > Merge Table

The Merge Table statement is a valid statement in some, but not all, databases. AQT's Merge function has been tested against DB2 for LUW, Oracle, SQL Server and PostgreSQL v15.

Understanding the Merge Statement

• The Merge statement is used to apply updates, inserts and deletes against a table (the Merge Into table).
• The data to be applied to the table comes from Merge From. This can be either a table or query. This table is not affected by the Merge.
• The rows in the two tables are matched by the Match Cols columns. These act much like the columns in a join. By default, AQT will populate these with the Primary Key columns for the tables, but you can specify other columns.

  Both the Merge Into and Merge From tables must specify the same number of Match Cols.

• Use Col Names to specify alias names of the Merge From columns.
• With the Merge Statement, you specify a number of Merge Actions. These are discussed below.
• When you click on Run, AQT will run the Merge.

  If you are using SQL Server and have specified the Output clause (on the Other tab), AQT will display the Merge output on a Data Display window.

Merge Actions

You need to specify at least one Merge Action. The main window allows you to specify two:

• When Matched. This action will be performed when a row is in both the Merge From table and the Merge Into table. Appropriate actions are:
  • Update Row. The row in the Merge Into table will be updated from values in the matching row of the Merge From table. In the grid, check Upd? to specify which columns are to be updated, and in Update From which columns in the Merge From table the data will come from.

    You can specify any expression for the update - examples: BONUS * 1.5, SALARY * 0.1, NULL, DEFAULT.

  • Delete Row. The row in the Merge Into table will be deleted when present in the Merge From table.
• When Not Matched. This action will be performed when a row is in the Merge From table, but not in the Merge Into table. There is only one action:
  • Insert. A row is inserted into the Merge Into table from values in the Merge From table.
• In the and box, you can specify an additional condition which needs to be met for the action to be performed.
• You can have multiple Merge Actions. Click on the More tab to be able to specify two more.

Notes

• Some databases do not report on the number of rows affected by the Merge:
  • Oracle returns no information on the number of rows affected
  • PostgreSQL will report that zero rows have been affected unless the Use SQLExecDirect option has been selected. If this option is selected, PostgreSQL will accurately report on the number of rows.
• Users of SQL Server 2017 may get the message:

  A MERGE statement must be terminated by a semi-colon (;)

  despite a semicolon being present. This is likely to be a bug in SQL Server; the problem is not present in earlier or later versions of SQL Server.

  You can circumvent this problem by going to the Other tab and checking the Output option.