How can I INSERT data into two tables simultaneously with only one sql script db2?

Question

How would I insert into multiple tables with one sql script in db2

For example, insert a row into T1 DOCK_DOOR and then insert into T2 DOCK_DOOR_LANE multiple times based on the dock_door_sysid from the first table.

My first approach was the following. I was attempting to use a with with three inserts. on the other hand, doing to inserts on the second table is not and option if this can be automated with one insert. thanks for any feedback

sql example

WITH ins AS (
  INSERT INTO DBF1.DOCK_DOOR    (DOCK_DOOR_SYSID,DOOR_NUMBER,DOOR_NAME,DOCK_SYSID,DOOR_SEQ,ENCRYPTION_CODE,RFID_ENBLD_FLAG,LANES_COUNT,CMNT_TEXT,CREATE_TS,CREATE_USERID,UPDATE_TS,UPDATE_USERID,VER_NUMBER,ACTIVE_FLAG,STATUS_SYSID,DOOR_TYPE_SYSID) 
VALUES (nextval for DBF1.DOCK_DOOR_SEQ,'026','DOOR025',61,25,NULL,'N','2',NULL,current timestamp,'SQL_INSERT',current timestamp,'SQL_INSERT',0,NULL,1723,1142)
  RETURNING door_number,dock_door_sysid),
ins2 AS (
INSERT INTO SIT.DOCK_DOOR_lane (DOCK_DOOR_LANE_SYSID,DOOR_LANE_ID,DOCK_DOOR_SYSID,LANE_ID,CREATE_TS,CREATE_USERID,UPDATE_TS,UPDATE_USERID,VER_NUMBER) 
VALUES (nextval for DBF1.DOCK_DOOR_LANE_seq,door_number||''||'A',dock_door_sysid,'A',current timestamp},'SQL_INSERT',current timestamp,'SQL_INSERT',0)
  SELECT door_number,dock_door_sysid FROM DBF1.DOCK_DOOR
  RETURNING door_number,dock_door_sysid)
INSERT INTO DBF1.DOCK_DOOR_lane (DOCK_DOOR_LANE_SYSID,DOOR_LANE_ID,DOCK_DOOR_SYSID,LANE_ID,CREATE_TS,CREATE_USERID,UPDATE_TS,UPDATE_USERID,VER_NUMBER) 
VALUES (nextval for DBF1.DOCK_DOOR_LANE_seq,door_number||''||'B',dock_door_sysid,'B',current timestamp},'SQL_INSERT',current timestamp,'SQL_INSERT',0)
SELECT door_number,dock_door_sysid FROM DBF1.DOCK_DOOR;

Table 1 = dock_door

Table 2 = Dock_door_lane

It's a good question, but I don't think there is an equivalent. In Oracle there is an INSERT FIRST and INSERT ALL. In SQL Server there is an OUTPUT clause that lets you chain multiple operations. Is there any reason you can't just wrap the INSERTs all in a transaction? — Cade Roux
– Cade Roux, Commented Mar 4, 2017 at 4:52
Thanks for feedback Cade. I was hoping that this might be possible with DB2, since inserting three consecutive inserts with SQL does solve this conundrum, but this is only helpful if only a few records need to be inserted. In a nutshell, I was hoping to avoid this without having to write a small program to automate the process. — Bobby Mor
– Bobby Mor, Commented Mar 4, 2017 at 6:35

Charles · Accepted Answer · 2017-03-05 18:17:16Z

2

You could do it with a trigger on the dock_door table.

However, if you're on a recent, version on IBM i. You might be able to make use of data change table reference

Your statement would look something like this

insert into dock_door_lane
  select <....>
    from final table (insert into dock_door <...>)

I'm not sure it will work, as this article indicates that at least at a couple of years ago DB2 for i didn't support the secondary insert required.

This old SO question also seems to confirm that at least at v7.1, the double insert isn't supported.

If I get a chance, I'll run a test on a 7.2 system Monday.

answered Mar 5, 2017 at 18:17

Charles

24.2k3 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charles Over a year ago

Running v7.2 TR 4, data change table reference isn't supported in the insert. I get a an SQ20165 error. I also don't see anything in the "What's new" section of v7.3 that would indicate the limitation was lifted.

Collectives™ on Stack Overflow

How can I INSERT data into two tables simultaneously with only one sql script db2?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related