Home » SQL & PL/SQL » SQL & PL/SQL » building dynamic SQL insert statement (Oracle 10g)
icon6.gif  building dynamic SQL insert statement [message #564437] Thu, 23 August 2012 14:22 Go to next message
aft5425
Messages: 5
Registered: October 2008
Junior Member
Ok, i'm working in an Oracle 10g database on an IBM AIX server.

I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.

I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".

I build the dynamic query using a for loop in my shell script (see below).

The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?


Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)

for tbl in `more Archive_Rates.txt`
do
echo 'BEGIN WORK; ' > rc1.sql
echo ' ' >> rc1.sql
echo 'insert into Master' >> rc1.sql
echo '(prod, rate, rate_exp) ' >> rc1.sql
echo ' select i_prod, c_rate, d_rate_exp ' >> rc1.sql
echo ' from ' $tbl >> rc1.sql
echo ' ' >> rc1.sql
echo 'COMMIT WORK; ' >> rc1.sql
echo ' ' >> rc1.sql
done

--more rc1.sql

BEGIN WORK

insert into Master
(prod, rate, rate_exp,
)
select i_prod, c_rate, d_rate_exp
from Table A

COMMIT WORK


BEGIN WORK

insert into Master
(prod, rate, rate_exp, <-------------------How do i dynamically insert the correct columns in Master table?
)
select product, rate, deductible
from Table B

COMMIT WORK

BEGIN WORK

insert into Master
(prod, rate, rate_exp, <-------------------How do i dynamically insert the correct columns in Master table?
)
select product, rider, gender, age, rate
from Table C

COMMIT WORK

Please help!
Re: building dynamic SQL insert statement [message #564438 is a reply to message #564437] Thu, 23 August 2012 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>How do i dynamically insert the correct columns in Master table?
From my perspective, there is nothing dynamic in this problem.
table A will always INSERT INTO MASTER the same collection of fixed columns.
table B will always INSERT INTO MASTER a different collection of fixed columns.

@TABLE_A.sql
@TABLE_B.sql

[Updated on: Thu, 23 August 2012 17:08]

Report message to a moderator

Re: building dynamic SQL insert statement [message #564469 is a reply to message #564437] Fri, 24 August 2012 03:18 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Also it would be simpler to put the whole thing inside a PL/SQL block and using EXECUTE IMMEDIATE (within a .sql script file)
Re: building dynamic SQL insert statement [message #564470 is a reply to message #564469] Fri, 24 August 2012 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?

Regards
Michel
Re: building dynamic SQL insert statement [message #564532 is a reply to message #564470] Fri, 24 August 2012 16:09 Go to previous messageGo to next message
aft5425
Messages: 5
Registered: October 2008
Junior Member
Ok, i'm updating my post to include additional info as required by the forum guidelines. Sorry for not doing this to start with.

Oracle version: 10.2.0.5
Unix server: AIX 6.1

Problem: I'm trying to build a dynamic SQL insert statement that works, no matter which table i'm selecting columns from using a sub select.
I have 3 child tables (tables A, B and C). I also have one primary table (Master).

Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
Master table which is used to store the data from Tables A, B and C via the insert statement contains all columns from each child table, although column names may be different. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".

Problem is that the shell script i'm using to build the insert SQL dynamically will need to change based on the child table that using to select columns from. For example, if i'm selecting data from Table A, the columns to be inserted into the Master table will need to change.

I'm reposted the SQL i use to generate the insert statement:

for tbl in `more Archive_Rates.txt`
do
echo 'BEGIN WORK; ' > rc1.sql
echo ' ' >> rc1.sql
echo 'insert into Master' >> rc1.sql
echo '(prod, rate, rate_exp) ' >> rc1.sql
echo ' select i_prod, c_rate, d_rate_exp ' >> rc1.sql
echo ' from ' $tbl >> rc1.sql
echo ' ' >> rc1.sql
echo 'COMMIT WORK; ' >> rc1.sql
echo ' ' >> rc1.sql
done

--more rc1.sql

BEGIN WORK

insert into Master
(prod, rate, rate_exp,
)
select i_prod, c_rate, d_rate_exp
from Table A

COMMIT WORK


BEGIN WORK

insert into Master
(prod, rate, rate_exp, <-------------------How do i dynamically insert the correct columns in Master table?
)
select product, rate, deductible
from Table B

COMMIT WORK

BEGIN WORK

insert into Master
(prod, rate, rate_exp, <-------------------How do i dynamically insert the correct columns in Master table?
)
select product, rider, gender, age, rate
from Table C

COMMIT WORK



Yes, you're correct BlackSwan, this isn't all that dynamic right now, but that's what i'm trying to fix. Make sure the columns inserted into the Master table are changed, depending on the child table that the select statement uses.
Re: building dynamic SQL insert statement [message #564533 is a reply to message #564532] Fri, 24 August 2012 16:35 Go to previous message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
echo ' ' > rc1.sql
for tbl in `more Archive_Rates.txt`
do
echo '@'${tbl}'.sql' >> rc1.sql
done
echo 'COMMIT;' >> rc1.sql
sqlplus username/password @rc1.sql
# where each "table_*.sql" file contain INSERT hardcoded for only its columns
Previous Topic: set forceplan on
Next Topic: PL/SQL mutating error (IFS)
Goto Forum:
  


Current Time: Thu Oct 23 05:29:22 CDT 2014

Total time taken to generate the page: 0.16821 seconds