SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Insert into a SQL table with identity column from DI Studio

Reply
New Contributor gn
New Contributor
Posts: 4

Insert into a SQL table with identity column from DI Studio

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?
SAS Employee
Posts: 36

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

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.
New Contributor gn
New Contributor
Posts: 4

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

Thanks for the response, although I need a bit more clarification on option 2.
SAS Employee
Posts: 13

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

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.
New Contributor gn
New Contributor
Posts: 4

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

thx much Michael. Really helpful.
Senior User
Posts: 1

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

[ Edited ]

Hi Michael,

 

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

Regards,

 

Ask a Question
Discussion stats
  • 5 replies
  • 1898 views
  • 0 likes
  • 4 in conversation