topic Re: Capturing a generated unique key when inserting records into a database in SAS Procedures

Capturing a generated unique key when inserting records into a database

CurtisMack — Thu, 22 Oct 2009 00:06:28 GMT

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

Re: Capturing a generated unique key when inserting records into a database

Patrick — Thu, 22 Oct 2009 11:12:42 GMT

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

Re: Capturing a generated unique key when inserting records into a database

GertNissen — Thu, 22 Oct 2009 13:00:34 GMT

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.

Re: Capturing a generated unique key when inserting records into a database

CurtisMack — Thu, 22 Oct 2009 22:07:27 GMT

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

Re: Capturing a generated unique key when inserting records into a database

GertNissen — Tue, 03 Nov 2009 12:03:21 GMT

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.