sqlmap insert into multiple tables at once

[ghesil]

i need to insert data into multiple tables from a single <insert> in an sqlMap. is there any way to do that?

the closest i got is something like

<insert id="insertData" parameterClass="Application.database.Data">
INSERT INTO
T1 (id,c1,c2)
VALUES (nextval('t1_id_seq'),#c1#,#c2#);

INSERT INTO
T2(id,t1id,c3,c4)
VALUES (nextval('cust_account_id_seq'),__what_here?__,#c3#,#c4#)
</insert>

but i don't know how to put the new generated key from t1 into t2 (where __what_here?__ is)

seleckKey was an option but couldn't make it work as expected

thank you very much

[DaMule]

You could try user defined variables

It may look somthing like this

Code:

<insert id="insertData" parameterClass="Application.database.Data">
INSERT INTO
T1 (id,c1,c2)
VALUES (nextval('t1_id_seq'),#c1#,#c2#);

SET @last_id = SELECT LAST_INSERT_ID();

INSERT INTO
T2(id,t1id,c3,c4)
VALUES (nextval('cust_account_id_seq'),@last_id,#c3#,#c4#)
</insert>

I've not test this or anything, just a guess.

[ghesil]

the ideea is nice, but i'm using postgress and it doesn't seem to work there (or i don't know the sintax)
thanks for the ideea anyway, i'll dig it more along this line

[ghesil]

found a nice way of doing it in postgress, as it doesn't seem to have user defined variables

it looks like

INSERT INTO
T1 (id,c1,c2)
VALUES (nextval('t1_id_seq'),#c1#,#c2#);


INSERT INTO
T2(id,t1id,c3,c4)
VALUES (nextval('cust_account_id_seq'),currval('t1_id_seq'),#c3#,#c4#);

thank you fir your answer DaMule

[bobgherasim]

Great stuff ghesil. Nice

[Ben]

Will there be something like the select-attribute in resultMap but for inserting data?
I mean anything that helps inserting complex properties and collections into DB in a similar way.

Maybe it can already be done using the typeHandler attribute, not sure about that...

But I think an additional insert attribute within the parameterMap would be more straight foreward.