BookmarkSubscribeRSS Feed
marzieh
Calcite | Level 5

Hi all

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

6 REPLIES 6
Ksharp
Super User

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;

Ksharp

marzieh
Calcite | Level 5

Dear Ksharp

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:

data three;

input name $ sex$ age@@ height@@ weight@@;

cards;

Patrick M 30 52.2 89.4

;

run;

proc sql;

  insert into one (name, sex, age, height, weight)

  select name, sex, age, height, weight

  from three;

select * from one

  except

select * from two;

quit;

run;

Ksharp
Super User

OK. Easy.

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;



Ksharp

marzieh
Calcite | Level 5

another question:

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?

Ksharp
Super User

Just as Tom said, I think you need some help from Oracle Administrator .

If you are familiar with Oracle ,why not do it in Oracle?

Ksharp

Tom
Super User Tom
Super User

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 914 views
  • 3 likes
  • 3 in conversation