How would I do this in SQL?

Question

Let's say I have the following tables: PartyRelationship EffectiveDatedAttributes

PartyRelationship contains a varchar column called class.
EffectiveDatedAttributes contains a foreign key to PartyRelationship called ProductAuthorization.

If I run this:

select unique 
        eda.productauthorization 
from 
        effectivedatedattributes eda  
inner join 
        partyrelationship pr
    on 
        eda.productauthorization = pr.ID  
where 
        pr.class = 'com.pmc.model.bind.ProductAuthorization'

it returns a list of ProductAuthorization IDs. I need to take this list of IDs and insert a new row into EffectiveDatedAttributes for every ID, containing the ID as well as some other data. How would I iterate over the returned IDs from the previous select statement in order to do this?

Answer 1

Actually insert can use the result from a select. Like:

insert into YourTable
(col1, col2, col3)
select col1, col2, col3
from OtherTable

it should be easy to adapt this to your situation.

Answer 2

You mean something like so?:

Insert EffectiveDatedAttributes ( Id, Col1, Col2....)
select unique eda.productauthorization, 'Attribute1', 'Attribute2'....
from effectivedatedattributes eda
    inner join partyrelationship pr
        on eda.productauthorization = pr.ID
where pr.class = 'com.pmc.model.bind.ProductAuthorization';

Or is it the case that you have other tables involved in what should be inserted?

EDIT Given the additional details you provided in comments, you will need to generate ID values that go into EffectiveDateAttributes. Here is a very of the query that would not require the use of Common Table Expressions:

Insert EffectiveDateAttributes( Id, Col1, Col2, Col3, ..., )
Select Distinct 
        (
        Select Count(*) + LastId.Id + 1
        From PartyRelationship As PR1
        Where PR1.class = 'com.pmc.model.bind.ProductAuthorization';
            And PR1.ID < PR.ID
        ) Id
    , PR.ID, 'Static Value 1', 'Static Value 2', PR.Col1, PR.Col2...
From PartyRelationship PR
    Join EffectiveDateAttributes EDA
        On EDA.ProductAuthorization = PR.ID
    Cross Join  (
                    Select Max(ID) Id
                    From EffectiveDateAttributes
                    ) As LastId
Where PR.class = 'com.pmc.model.bind.ProductAuthorization'

I'm still not quite convinced I need the join to EffectiveDateAttributes. It's only purpose is to ensure that any values we evaluate in the PartyRelationship table exist in the EffectiveDateAttributes table. If that does not matter, then it can be removed.

Second, I generated a sequence by querying for the count of all values of PartyRelationship less than the current value. That will give me a sequence of numbers starting at zero. I then add the highest ID value that currently exists in EffectiveDateAttributes and add one and that should give us the next available ID ignoring various transaction isolation issues. There is a simpler method which I'll post shortly.

Also note that in the select statement, you can include additional static values that are used to insert into your table. In your original query I notice that you used the word "unique" instead of Distinct. The ANSI standard word for ensuring uniqueness in the output of a select statement is Distinct and Oracle will recognize this.

EDIT Using a Common Table Expression, makes this a bit simpler:

With NumberedTables As
    (
        Select PR.ID
            , ROW_NUMBER() OVER( ORDER BY PR.ID ) As Num
        From PartyRelationship PR
            Join EffectiveDateAttributes EDA
                On EDA.ProductAuthorization = PR.ID
        Where PR.class = 'com.pmc.model.bind.ProductAuthorization'
        Group By PR.ID
    )
    , LastIdUsed As
    (
        Select Max(ID) Id
        From EffectiveDateAttributes
    )
Insert EffectiveDateAttributes( Id, Col1, Col2, Col3, ..., )
Select Num + LastIdUsed.Id,  'Static Value 1', 'Static Value 2', PR.Col1, PR.Col2...
From NumberedTables 
    Join PartyRelationship PR
        On PR.ID = NumberedTables.ID
    Cross Join LastIdUsed

Answer 3

I concur with Thomas, The select statement you have already got can form the basis of a separate table (virtual table).

So do this:

INSERT INTO EvvectiveDatedAtributes(VirtualTable.productauthorization , data, data, data)
FROM 
    (YourQueryHere) as VirtualTable