Data Integration with the Merge Table Step in SAS Studio Flows
With the Merge Table step, you can update a target table with data in a source table. The Merge Table step allows you to update values in existing rows, insert new rows, or do both within one step. There are multiple benefits to this, including reducing the number of steps in your flow and optimizing processing via in-database execution.
To use the Merge Table step, you'll need a SAS Studio Engineer license. Additionally, this step can only be used with Oracle, Teradata, Snowflake, and SQL Server tables at the time of publication. To see the most current list of acceptable data types, refer to the documentation.
For my example, I'll be using the target table MARKETPRODUCTS which contains information for products being sold at a market and the source table MARKETPRODUCTUPDATE which contains updated product information.
I start by adding MARKETPRODUCTUPDATE to the Flow canvas.
Then, I add the Merge Table step and connect the source table to the input port.
After the source table is connected, I select my target table MARKETPRODUCTS on the Target Table tab. Note that you must select a table that is the same type and from the same database as your source table.
Once my tables are selected, I can configure my merge conditions on the Options tab. The first step is to choose a key column from the target table, which is the column used to match rows between the source and target tables. The key column must have a matching column in the source table, and it must contain unique identifying information for each row. I choose the ID column, which contains each product's unique numeric ID.
Next, I add my update and insert conditions. You must add at least one update or insert condition for the Merge Table step to run, but you do not have to choose both. In the Update section on the Options tab, I'll choose to update the values in the Product and Price columns.
Note here that the source table does not have a Price column, but it has a column named Cost which contains the same information, so I manually select them to map, or match together.
In the Insert section, I choose all columns to insert new row values into. You have the freedom to select only a few columns if you don't want full new rows to be inserted into your target table. For example, I could choose to include only ID and Product values in new rows if I plan to add a new price later.
I can look at column attributes of my target table on the Column structure tab.
I can also check the column resolution between my source and target tables on the Column resolution tab. Note that you cannot adjust column mapping on this tab, but you can view it. Here, I can see that the Price column does not have a match in the source table and the Cost column is ignored during mapping because it does not exist in the target table.
Lastly, I can see a snippet of my target table data on the Preview Data tab. This is the target table before the flow has run.
After running the flow, I can view the changes made to my target table. I can see updated values and new rows.
SAS Studio automatically generates code for this flow, which you can view for reference or save for future use.
What about the Load Table step?
For those who are familiar with the data integration steps in Flows, you might find that this step's functionality sounds similar to the Load Table step. So, what's the difference?
The Load Table step does not have a limit on what type of table can be used with it, while the Merge Table step does. However, the Load Table step does not have the column flexibility that the Merge Table step does. Any source columns that do not match exactly with target columns will be ignored during loading and you cannot manually match columns in the Options tab. Additionally, you cannot manually select columns to update or insert into. Lastly, Load table has multiple output table options (and preprocess options if you're loading with the insert rows technique) while Merge Table does not.
For more information on Load Table, view this post.
In summary...
The Merge Table step can be used to update, insert, or upsert values in your Oracle, Teradata, Snowflake, or SQL Server tables. The flexibility and simplicity of this step makes it a great addition to your point-and-click data integration toolbelt!
For more information on this step, refer to the documentation. An accompanying video demonstration can be found here.
Find more articles from SAS Global Enablement and Learning here.
