Last_Insert_id() from Proc SQL?

deleted_user — Wed, 25 Mar 2009 11:12:59 GMT

Hi all,

I'm looking for a way to store the last auto id that was inserted into a mysql database.

Basically, I need to insert a new observation into a database using SAS. The obervation will get an auto id once inserted. I required this auto id for inserting another observation into a different table.

I'm just wondering, I'm guessing I can only use

select LAST_INSERT_ID() as id

when using pass-thru sql, how am I to get this value out into the local sas when my data to be inserted is stored locally. I've got a local table in work, that I need to insert into a remote table, but I will required the auto id from one insert instance to be used for the next insert instance.

Or is it possible to use pass-thru sql to read from a local table and insert data into remote table?

Thanks for any help.

Re: Last_Insert_id() from Proc SQL?

GertNissen — Wed, 25 Mar 2009 13:35:46 GMT

If you don't find another solution, then try this rather ugly hack 🙂

PROC SQL;

connect to ODBC as mssql(noprompt="DRIVER=SQL Server;Server=SQLserver;DATABASE=SQLdatabase");

execute ( INSERT INTO dbo.DWtable (status) VALUES ('text') ) by mssql;

execute ( create table #temp (TempTableField int)) by mssql;

execute ( INSERT INTO #temp SELECT IDENT_CURRENT('dbo.DWtable')) by mssql;

create table temp as select * from connection to mssql (select max(TempTableField) as max from #temp);

disconnect from mssql;

QUIT;

topic Last_Insert_id() from Proc SQL? in SAS Programming

Last_Insert_id() from Proc SQL?

Re: Last_Insert_id() from Proc SQL?