SAS Viya: SAS Studio Flow Load Table Step
The Load Table step enables you to load a source table into a target table. With the Load Table step, you can control how data is loaded into the target table. You can insert new source rows into the target table, update existing rows in the target table, or both. Also, if needed you can control how existing rows in the target table are removed before new rows are inserted. The use of the Load Table step requires a SAS Studio Engineer license.
The example I am going to walk through in this blog is inserting records into an existing table using the Load Table step.
I have a file of new records to insert into the PRDSALE table in my SASDM library. The file has 720 new records to insert in the existing PRDSALE table with 1440 records, so after inserting the new records the updated PRDSALE table should have 2160 records.
I add the file with the new records to be inserted and import it in a SAS Studio Flow. Note: Currently the input data to be inserted/updated must have column names exactly match the ones you want to affect in the target table.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Now, I add the Load Table step from the Integrate section to the flow.
On the Target Table tab of the Load Table step, I select the Library and Table name where the new records are to be inserted.
On the Options tab, I select Insert rows for the load technique. If I wanted to Update rows or Upsert (update and insert) rows, then I would also need to specify the row(s) that uniquely identify a record in the table (primary key). I select No action for the preprocess action. If needed I could choose to truncate table, delete all rows, or replace the table if it exists instead. Depending upon the target table type, there may also be Output Table Options for selection, or I could manually specify some by selecting the Advanced table options link.
On the Column Structure tab, you can review the structure of the target table.
On the Column Resolution tab, the input columns are mapped to the target columns based on name. The column RECORD_TYPE column in the input file does not exist in the target table, so it is ignored and the PREDICT column does not exist in the input file.
On the Preview Data, you can preview the current state of the target table.
I run my flow and when I review the resulting PRDSALE table in the SASDM library, there are now the expected 2160 records. The new records have been inserted into the table.
Summary
The Load Table step provides a way to update, insert, or upsert (update and insert) data into a table. For more information on this step, please refer to its documentation.
Find more articles from SAS Global Enablement and Learning here.
