We are still researching this question, but here is some information. We are researching if the DI Studio loader can be made to generate this code for you. In the meantime, here are some options to explore. These are a couple of ways to accomplish this, and there may be others. We will post again once we have investigated the loader option.
1. You can use proc append to insert rows into an RDBMS table that includes sequence (auto number) . To accomplish this, simply drop the column from the base table when appending.
Example:
proc append base=target.my_target(drop=SQL_ID)
data=source.my_source;
run;
This syntax would not actually result in physically dropping the column SQL_ID from the target table. Instead, this will result in preparing the SQL insert query correctly by not including a reference to the SQL_ID column in the prepared query.
This method should work for other RDBM’s that support sequences such as DB2, Oracle and Teradata.
2. One other method would be to add pre/post process to turn off and then turn on sequencing. An explicit pass-through query turning off sequencing could be executed in a pre-process step. A post-process query would then turn sequencing on.
note: The proc append method would be a less complicated method as you would not have to turn sequencing off and on.