Inserting multiple records in database table using PK from another table

Question

I have DB2 table "organization" which holds organizations data including the following columns

organization_id (PK), name, description

Some organizations are deleted so lot of "organization_id" (i.e. rows) doesn't exist anymore so it is not continuous like 1,2,3,4,5... but more like 1, 2, 5, 7, 11,12,21....

Then there is another table "title" with some other data, and there is organization_id from organization table in it as FK.

Now there is some data which I have to insert for all organizations, some title it is going to be shown for all of them in web app. In total there is approximately 3000 records to be added.

If I would do it one by one it would look like this:

INSERT INTO title 
(
    name, 
    organization_id, 
    datetime_added, 
    added_by, 
    special_fl, 
    title_type_id
) 
VALUES 
(
     'This is new title', 
     XXXX, 
     CURRENT TIMESTAMP, 
     1, 
     1, 
     1
);

where XXXX represent "organization_id" which I should get from table "organization" so that insert do it only for existing organization_id. So only "organization_id" is changing matching to "organization_id" from table "organization".

What would be best way to do it? I checked several similar qustions but none of them seems to be equal to this? SQL Server 2008 Insert with WHILE LOOP While loop answer interates over continuous IDs, other answer also assumes that ID is autoincremented.

Same here: How to use a SQL for loop to insert rows into database?

Not sure about this one (as question itself is not quite clear) Inserting a multiple records in a table with while loop

Any advice on this? How should I do it?

Don't loop in SQL, you can insert from a select statement. Instead of values() write a select statement that lists the data you want to enter. IE : Insert into tbl(listcolumn) select (same columns) from tbl1 inner join tbl2 on tbl1.key = tbl2.key — Twelfth, Aug 08 '16 at 19:57
how do you identify what the organization_id is? you say you want to get it from the organization table but the join/relationship is organization_id that is fine but what will you use to look it up? organization name? perhaps providing some sample data and expected result we will understand what you are trying to accomplish better — Matt, Aug 08 '16 at 20:00
@Matt Sample SQL is exactly what I have. organization_id is PK in both tables. One (organization) is the one which holds data for organization, other holds message that is displayed for each organization when their users log in. Right now I have to populate all of them with same message/tile (what I wrote as 'This is new title'). — Nenad Bulatović, Aug 08 '16 at 20:05
oaky if you want a record for every record in organization see me answer below. If that is not what you want. I reiterate to provide sample data and expected result. — Matt, Aug 08 '16 at 20:07

Matt · Accepted Answer · 2016-08-08T20:09:33.480

If you seriously want a row for every organization record in Title with the exact same data something like this should work:

INSERT INTO title 
(
    name, 
    organization_id, 
    datetime_added, 
    added_by, 
    special_fl, 
    title_type_id
) 
SELECT 
    'This is new title' as name, 
    o.organization_id,
    CURRENT TIMESTAMP as datetime_added, 
    1 as added_by, 
    1 as special_fl, 
    1 as title_type_id
FROM
    organizations o
;

you shouldn't need the column aliases in the select but I am including for readability and good measure. https://www.ibm.com/support/knowledgecenter/ssw_i5_54/sqlp/rbafymultrow.htm

and for good measure in case you process errors out or whatever... you can also do something like this to only insert a record in title if that organization_id and title does not exist.

INSERT INTO title 
(
    name, 
    organization_id, 
    datetime_added, 
    added_by, 
    special_fl, 
    title_type_id
) 
SELECT 
    'This is new title' as name, 
    o.organization_id,
    CURRENT TIMESTAMP as datetime_added, 
    1 as added_by, 
    1 as special_fl, 
    1 as title_type_id
FROM
    organizations o
    LEFT JOIN Title t
    ON o.organization_id = t.organization_id
    AND t.name = 'This is new title'
WHERE
    t.organization_id IS NULL
;

Inserting multiple records in database table using PK from another table

1 Answers1