April 8, 2011 at 2:06 pm
Is it possible to have 2 sql statements in 2 ole db command executed at same time. what i am trying to achieve is soon after the insert to get the value back the value of the ID column. is this possible? ANy thoughts, inputs ideas will be appreciated.
INSERT INTO HA.aaa(
ID,
person_id,
HLTH_id,
VER_NBR,
CREAT_BY,
UPDT_BY,
CREAT_TS,
UPDT_TS
)
VALUES( HA.A.nextval,
?,?,?,?,?,
?,
?);
select ha.AA.currval from dual
go;
April 8, 2011 at 2:34 pm
I have not tried it within SSIS but the OUTPUT clause will generate a resultset for you with your ID that you may be able to pipe into a variable as a resultset:
INSERT INTO HA.aaa
(
ID,
person_id,
HLTH_id,
VER_NBR,
CREAT_BY,
UPDT_BY,
CREAT_TS,
UPDT_TS
)
OUTPUT INSERTED.currval
VALUES
(
HA.A.nextval,
?,
?,
?,
?,
?,
?,
?
);
edit: curiosity got to me...I tested it and it works
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 8, 2011 at 3:21 pm
Great this is what something i was looking for. so will it give a currval before the insert goes thru? lets say i have 100 recs after insert there will be 101 my output needed is 101. so that how it shows up when you tried it. you feedback is greatly appreciated..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply