CODEWITHSUNDEEP
sqldatabasedb2ibm-midrange
techie
techie

Reputation: 467

SQL insert into 2 tables in one query

I have the following query in SQLRPGLE for DB2:

    INSERT INTO ITEMS2 (PROGRAM, VLDFILE, VLDFLD, 
    SELFILE, SELFLD) VALUES(:SCAPP   , 'CSTMR', 'CYC',
    'BYC', 'BYCC');

I would like this query to be run in 2 libraries as in FIRST/ITEMS2 and SECOND/ITEMS2

where FIRST and SECOND are the library names. Can this be achieved in one query?

For those who have no understanding of iSeries: The above insert statement would be similar to having a insert query for 2 tables.

Upvotes: 1

Views: 5844

Answers (4)

contradictioned
contradictioned

Reputation: 1253

From IBM's Syntax diagram of INSERT ( http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzbackup.htm ), I'd say you have to go with two queries.

But after the first time of executing this query, you can try changing the current library ( http://publib.boulder.ibm.com/infocenter/iadthelp/v7r1/topic/com.ibm.etools.iseries.langref2.doc/chglibl.html ).

Upvotes: 0

WarrenT
WarrenT

Reputation: 4532

You could create a QMQuery like this

    INSERT INTO &LIB/ITEMS2 
             (PROGRAM, VLDFILE, VLDFLD, SELFILE, SELFLD)
      VALUES (&SCAPP, 'CSTMR',  'CYC',  'BYC',   'BYCC');

Then

STRQMQRY myQmQry  SETVAR(('LIB' 'FIRSTLIB')('SCAPP' &VAR))
STRQMQRY myQmQry  SETVAR(('LIB' 'SECONDLIB')('SCAPP' &VAR))

Upvotes: 1

Buck Calabro
Buck Calabro

Reputation: 7648

If this will be run often, consider making the INSERT into a stored procedure, and then setting the target schema via SET SCHEMA:

set schema=first;
call my_insert_proc(:scapp);
set schema=second;
call my_insert_proc(:scapp);

Upvotes: 1

James Allman
James Allman

Reputation: 41168

The INSERT statement does not support inserting into multiple tables.

However you could create a trigger on FIRST/ITEMS2 to automatically insert/update/delete the record into SECOND/ITEMS2.

See the CREATE TRIGGER statement for more information.

Upvotes: 2

Related Questions