Data Integration Studio Archive Table

DavidPhillips2 — Wed, 04 Sep 2019 16:54:29 GMT

How do you set up an archive table job in Data Integration Studio? I need to insert new rows nightly and keep the old rows. I'm playing with the append transformation and merge transformation and having a hard time with both. It looks like the merge transformation is the way to go. For the merge transformation, I selected all columns from the source table on the insert tab. I selected only the composite key columns on the match tab. I know I'm missing a step.

The error message is One or more expression(s) are invalid. Please fix the expression(s). the insert statement is incomplete: Both the left and right operands are missing. Fix the invalid expression. The insert statement is incomplete: A set expression is missing a field or value.

Re: Data Integration Studio Archive Table

Patrick — Wed, 04 Sep 2019 21:42:42 GMT

@DavidPhillips2

As far as I understand things the Merge transformation is creating SQL MERGE code which allows you to combine INSERT and UPDATE in a single SQL.

From what you describe you need only INSERT. If you've got the delta records already then I'd be using the Append transformation with append to existing, if you first have to work out the delta records then I'd be using the SQL INSERT transformation.

topic Data Integration Studio Archive Table in SAS Data Management

Data Integration Studio Archive Table

Re: Data Integration Studio Archive Table