BookmarkSubscribeRSS Feed
gn
Calcite | Level 5 gn
Calcite | Level 5
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?
5 REPLIES 5
nar_sas
SAS Employee
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.
gn
Calcite | Level 5 gn
Calcite | Level 5
Thanks for the response, although I need a bit more clarification on option 2.
Michael_SAS
SAS Employee
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.
gn
Calcite | Level 5 gn
Calcite | Level 5
thx much Michael. Really helpful.
Balli
Obsidian | Level 7

Hi Michael,

 

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

Regards,

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 5030 views
  • 0 likes
  • 4 in conversation