Solved

may be stored procedure.

Posted on 2014-01-22
11
263 Views
Last Modified: 2014-02-06
I Have Table T1(A,B,C) and I have a Table t2(D,E,F).

when ever i want to insert on table t1 in coulmn A,B,C. I want to insert it into table T2 same value for clumns in T1 + may be  appending some additional value to table t2 or .

what would be the best approach and can i sample code for it.

for ex:
if insert (USA,UK,IND) in to T1 i need to pass values and append 001 for USA and 044 to uk and 091 to IND and it should insert like (USA001,UK044,IND091).

in above example 001,044,091 should be any value i want to append it to the value in table T1.
0
Comment
Question by:ajaybelde
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39800887
An insert trigger on t1 might work.

Where are you getting the values to append?

I don't see how a stored procedure would work unless you have some 'new' flag in t1 that the procedure can process in batch mode.
0
 

Author Comment

by:ajaybelde
ID: 39800892
its manual
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39800897
So how do you know what records in t1 need manual values added to t2?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39800908
something like this?


CREATE OR REPLACE PROCEDURE insert_two_table(
    p_a   IN VARCHAR2,
    p_b   IN VARCHAR2,
    p_c   IN VARCHAR2,
    p_d   IN VARCHAR2,
    p_e   IN VARCHAR2,
    p_f   IN VARCHAR2
)
IS
BEGIN
    INSERT INTO t1(a, b, c)
         VALUES (p_a, p_b, p_c);

    INSERT INTO t2(d, e, f)
         VALUES (p_a || p_d, p_b || p_e, p_c || p_f);
END;
0
 

Author Comment

by:ajaybelde
ID: 39800934
@sdstuber
does  p_a ,p_b,p_c   is also need to be passed Manual?.

my req is whenver we insert  p_a ,p_b,p_c on t1(which is not manual)  it should insert VALUES in t2 (p_a || p_d, p_b || p_e, p_c || p_f) and i should able pass the values p_d, p_e, p_f manually where ever required
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39800955
if a non manual process inserts a,b,c to t1
how do you imagine a user could possibly be prompted to manually enter d,e,f?

The request doesn't make sense to me.
0
 

Author Comment

by:ajaybelde
ID: 39801096
if it is manual for all values. how would i pass 6 values to SP
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39801126
>> if it is manual for all values. how would i pass 6 values to SP

begin
 insert_two_table('USA','UK','IND','001','044','091');
end;
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39801131
>>if it is manual for all values. how would i pass 6 values to SP

You need some way to flag the new rows in t1.  How do you plan on knowing what rows in t1 need to have some value appended in t2?   Will ALL the new values in t1 get appended with the same values in t2?

What does appending a value give you?

I would think the design would be something like: t2(A,B,C,D,E,F).

Then a trigger on t1 could pre-populate A,B and C in t2.  Then all you need to do is look for null values for D,E and F in t2 to get what rows need processing.

Even if you need them concatenated, you can create a view off of t2 to perform the concatenation:

create or replace view t2_vw as
select a||d a_d, b||e b_e, c||f c_f from t2 where d||e||f is not null;
0
 
LVL 32

Expert Comment

by:awking00
ID: 39803519
What does the appended value do? From your example, it looks like it could be a numerical representation of the country code. That would mean for every value in T1, there would be a corresponding append value, which could be managed by a lookup table of some sort. If that's not the case and the values that get appended are truly random or at the mercy of someone's imagination, what good do they do? If there is some sort of criteria that define the values to be appended, I think you might still accomplish your intent with an after insert trigger that applies the criteria logic.
0
 

Author Closing Comment

by:ajaybelde
ID: 39839913
they are planning to prevent it from application logic
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle regular expression 6 47
C# Connection String for Oracle database is not working 22 93
Oracle and DateTime math 6 26
oracle 11g 23 51
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now