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
... View more