topic Re: Insert into a SQL table with identity column from DI Studio in SAS Data Management

Insert into a SQL table with identity column from DI Studio

gn — Wed, 16 Feb 2011 00:10:49 GMT

I would like to know how can I insert into a SQL table with identity column using DI studio? I know this can be done in SQL using SET IDENTITY_INSERT [LOG] ON/OFF.
Any suggestion?

Re: Insert into a SQL table with identity column from DI Studio

nar_sas — Fri, 18 Feb 2011 16:01:50 GMT

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.

Re: Insert into a SQL table with identity column from DI Studio

gn — Fri, 18 Feb 2011 17:07:40 GMT

Thanks for the response, although I need a bit more clarification on option 2.

Re: Insert into a SQL table with identity column from DI Studio

Michael_SAS — Fri, 18 Feb 2011 21:01:48 GMT

It is pretty easy, first in SAS when you create your connection turn on the ignore_read_only_columns in SAS management console when you setup the Libname it is just a libname option, by default it is set to NO. It is counter intuitive but by ignore_read_only_columns = yes lets SAS ignore mapping stuff to identity columns.

libname srv odbc user=maimes pass=’XXXX’ DATASRC=SQLServerExpress ignore_read_only_columns=yes;

in your job use a loader and just don't map anything to your identity column

I attached a doc with some screen shots and info on how to use sequences as well.

Re: Insert into a SQL table with identity column from DI Studio

gn — Fri, 18 Feb 2011 21:14:36 GMT

thx much Michael. Really helpful.

Re: Insert into a SQL table with identity column from DI Studio

Balli — Thu, 13 Jul 2017 03:19:19 GMT

Hi Michael,

I could not find any attachment with your post. If possible, could you please provide me the attachment?

Regards,