Nenad Bulatovic Nenad Bulatovic - 3 months ago 7
SQL Question

Inserting multiple records in database table using PK from another table

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?

Answer

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
;
Comments