12-16-2014 09:46 AM
I would like to append Table A and Table B together, and use that newly appended table to overwrite Table A. The purpose of this is to have Table A be updated weekly with the data in Table B within an EG flow, so Table A is up to date when the flow is run. We need Table A to be current, as this is the table that will be appended to and used as the base file.
We tried exporting the appended table as a step in the process, however it only allows us to overwrite Table A once. When running the flow additional times, Table A is locked and we cannot append any new Table B's after that.
How can we export the appended table and overwrite Table A each time this flow needs to be run? We also need to make sure that when debugging the flow and needing to run it multiple times in one session, we do not perpetually append onto Table A, and would like to put a check in place to see if Table B has already been appended once, and only once.
12-16-2014 10:01 AM
Sounds like you're just inserting Table B into Table A? That can be done in an EG workflow with either the append task or a code node:
insert into TableB
select * from TableA;
12-16-2014 10:30 AM
You most probably have the appended table open as a result of the export. The best option to prevent this is to close the server connection after each run.
In order to prevent duplicates, one option is to include data that identifies each run (date, name of table to append, ...) in the data and filter that out while appending:
&table_a (where=(indicator ne "&table_b"))
if in_b then indicator = "&table_b";
12-16-2014 01:36 PM
I think you can get what you need with just a proc append and if you no longer need table B you can just delete all records from table B:
proc append base=Table_A data=Table_B force;
12-16-2014 04:42 PM