BookmarkSubscribeRSS Feed
CurtisMack
Fluorite | Level 6
Hi all,

I am hoping someone has a neat trick for doing this.
I am inserting rows into a SQL Server database table that has an "Identity" column, which Oracle would call a column with a default value generated by a sequence. In either case, I would like to post new records into that table and capture the DBMS generated key in the process. I have tried various configurations of the MODIFY statement, but SQL Server complains that "Cannot insert the value NULL into column ..." if that column is included in the DSV. Unfortunately I am stuck with the ODBC SAS/Access engines. I could of course do another pass through the data, but that is time consuming, and it assumes that I have an alternate unique key on which to match values. I recall something about a "RETURNING" clause in SQL but could not find in mentioned in the SAS docs.

Any help would be much appreciated,
Curtis
4 REPLIES 4
Patrick
Opal | Level 21
Hi Curtis

I doubt that it's possible to capture an auto generated key while inserting rows.
If there is a way then I would assume you should investigate SQL Server proprietary SQL syntax - and then load your data and read the key using pass-through SQL.

May be an after insert trigger could do the trick - but that's something you would have to implement on the SQL server database itself.

HTH
Patrick
GertNissen
Barite | Level 11
Hi Curtis

This sample will use MS SQL IDENT_CURRENT which is set when inserting rows into a table using a identity column.

PROC SQL;
connect to ODBC as mssql(noprompt="DRIVER=****;Server=******;DATABASE=*****");
execute ( INSERT INTO dbo.SQLTABLE(status) VALUES ('text') ) by mssql;
execute ( create table #temp (TempTableField int)) by mssql;
execute ( INSERT INTO #temp SELECT IDENT_CURRENT('dbo.SQLTABLE')) by mssql;
create table temp as select * from connection to mssql (select max(TempTableField) as max from #temp);
disconnect from mssql;
QUIT;

The table #temp is temoporary and will be deleted automatically by MS SQL.

Perhaps this will help you further http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60096

I dont know how this is done in Oracle, but perhaps Scope_Identity() is what you are looking for.
CurtisMack
Fluorite | Level 6
Thanks Geniz!

This is not quite as elegant as I was hoping for, but it definately does the trick. Since I am loading data from a dataset, I was particularly happy that it works even if the INSERT statement is not inside an EXECUTE block. That meant that I did not have to write a macro to build all of those VALUES statements with all of the variable values specified. All I needed was a MACRO loop to insert one row at a time using and firstobs and obs statements. This probably assumes that I am the only one posting to that table, but in my case that is a safe assumption.

Thanks Again

Here is the code I am using. It turns out I didn't need the temporary tables.


PROC SQL;
connect to ODBC as mssql(noprompt="Driver={SQL Server Native Client 10.0};Server=**.***.**.**\AAAAA,*****;database=******;uid=**;pwd=************;");

%macro dumpdata(datasetIn);
create table &datasetIn._out
as select 1 as PremiseId,PhoneNumber,TradeName,AddressId,Latitude,Longitude,EstablishmentDate
from &datasetIn
where 0 = 1;
%let dsid = %sysfunc(open(&datasetIn,IS));
%let obscount = %sysfunc(attrn(&dsid, NLOBS));
%let rc = %sysfunc(close(&dsid));
%do obnum = 1 %to &obscount;
insert into GEMSDB.Premises(PhoneNumber,TradeName,AddressId,Latitude,Longitude,EstablishmentDate)
select PhoneNumber,TradeName,AddressId,Latitude,Longitude,EstablishmentDate
from &datasetIn(firstobs = &obnum obs = &obnum);

select ThisId
into :ThisId
from connection to mssql (SELECT IDENT_CURRENT('dbo.Premises') as ThisId);

insert into &datasetIn._out
select &ThisId as PremiseId,PhoneNumber,TradeName,AddressId,Latitude,Longitude,EstablishmentDate
from &datasetIn(firstobs = &obnum obs = &obnum);
%end;
disconnect from mssql;
%mend;
%dumpdata(NewPremises);
QUIT; Message was edited by: Curtis Mack
GertNissen
Barite | Level 11
Hi Curtis

Tx for posting your code - It's always nice to see the final solution.
I think I will use some of your code in the future.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1978 views
  • 0 likes
  • 3 in conversation