12-26-2011 02:39 AM
I want to insert a record to a table in oracle and read its ID just after inserting it.
I don't want to lose my record while so many records might be inserted at the time.
How can I do that?
Thanks a lot
12-26-2011 03:43 AM
Make a counterpart of original dataset, then using EXCEPT operation to see what record you have inserted.
data one;set sashelp.class;run; data two;set sashelp.class;run; proc sql; insert into one values('Patrick','M',30,52.2,89.4); select * from one except select * from two; quit;
12-26-2011 05:57 AM
I read the values from table 'three' and insert it to 'one', how can I keep the rsult of (select *... ) in a table? It seems that it doesn't understand the code.
my code is:
input name $ sex$ age@@ height@@ weight@@;
Patrick M 30 52.2 89.4
insert into one (name, sex, age, height, weight)
select name, sex, age, height, weight
select * from one
select * from two;
12-26-2011 06:33 AM
data one; set sashelp.class; run; data two; set sashelp.class;run; data three; input name $ sex$ age height weight; cards; Patrick M 30 52.2 89.4 Peter M 40 54.3 100.2 ; run; proc sql; insert into one select * from three; create table want as select * from one except select * from two; quit;
12-26-2011 07:30 AM
Does this code guarantee that we read the inserted record?
I mean if another data inserted from another procedure while our proc sql is running, does it guarantee that the result is our record?
this procedure lock the table while runing or not?
12-26-2011 03:39 PM
Why do you need to know the ID number generated by the Oracle database?
If you are not competing against other proceses then just query the database to find the record you just inserted. Otherwise if you really need to get the number that Oracle generated you might need to trigger a stored procedure in Oracle that would insert the record and return the new ID as the result.