This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,805 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Append Tablename dynamically for Insert Statements

User_FRTCM
User_FRTCM Member Posts: 116 Blue Ribbon
edited Mar 31, 2014 11:04AM in SQL & PL/SQL

Hi,

I need a hand on this issue as it is critical for the phase we are in of our project.

Scenario :-

We have about 200 products which are having 10 tables each. Each Product is having the same name of tables but the columns differ for each product.

For example,

My Product 'Alfa' has a table XYZ which is having columns as 1,2,3,4,5,6,7,8,9,10 while the other product 'Beta' has table XYZ with columns as 1,2,3,4,5,11,12,13,14,15.

There are other tables also as XYZ like PQR, UVW etc ( 10 for each product ) which are common for all 200 products but the columns inside these tables are different.

I hope am clear with the scenario.

Current Architecture :-

Source (Oracle) - VB Codes - Destination (Access MDBs - Product Wise)

Each Access MDB is having 10 tables for each product coz of which same tablename have been maintained across all the products.

Note: The Tables are same for all products, its only the columns that are changing for each product.

Architecture After Migration :-

Source (Oracle) - PL/SQL Package + Procedures - Destination (Oracle - Tables in the form of <Product>_<tablename>)

I need help on the below :-

Insert into <Product>_<tablename> values () where Product has to be appended dynamically for each product.

I want to have Product ID as a changing dynamic value like

Insert into Alfa_XYZ values();

Insert into Beta_XYZ values();

Kindly suggest me a solution, I am trying from my end too. It would be appreciable if you could share a sample query/procedure/function to do this.

Thanks

PabloleeStew Ashton
«1

Answers

  • Pablolee
    Pablolee Member Posts: 405 Bronze Badge

    RIGHT EVERYONE. DROP WHAT YOUR DOING! THIS POSTER HAS AN URGENT REQUIREMENT. C'MON, NONE OF YOU HAVE JOBS WHICH HAVE HIGHER PRIORITY THAN THIS POSTER'S REQUIREMENTS. HOP TO IT!

  • Karthick2003
    Karthick2003 IndiaMember Posts: 13,711 Bronze Badge
    edited Mar 31, 2014 7:32AM
    I need help on the below :-
    Insert into <Product>_<tablename> values ();
    
    I want to have Product ID as a changing dynamic value like
    Insert into Alfa_XYZ values();
    Insert into Beta_XYZ values();
    
    
    

    I don't understand the question here. What do you want to do? Any way each table will have different set of columns. So how you are going to supply the VALUES part?

    Please read and explain your requirement in a better understandable way.

  • Nimish Garg
    Nimish Garg Sr. Database Developer @ Gartner Noida, IndiaMember Posts: 3,185 Gold Trophy

    If you are migrating the tables manually, move them in oracle per product wise, and rename them by adding prefix using

    rename tab_old to tab_new;

    you may write a pl/sql block for renaming tables.

  • Stew Ashton
    Stew Ashton Database Developer Member Posts: 2,919 Bronze Crown

    Right now, is the source data in Access or Oracle? If it is in Access, you should ask the question in an Access forum.

    Will the target tables have the same structure? In other words, will Alpha_XYZ have the same structure as Beta_XYZ? If so, I recommend having one XYZ table with a product_id column, then maybe partition by product_id. That way you divide by 200 the code you have to write and the parsing Oracle will have to do.

    You shouldn't have to create INSERT with VALUES statements at all. I suggest you look at the Migration tools in Oracle SQL Developer:

    Oracle® SQL Developer Supplementary Information for Microsoft Access Migrations Release 3.0

  • Paul  Horth
    Paul Horth London, UKMember Posts: 3,402 Gold Trophy

    It might be worthwhile making each product a schema in Oracle and having the corresponding tables in each schema.

    You don't need dynamic SQL - as Stew said, use the Migration tools.

    Finally, if you have a critical urgent requirement for your project - get paid support, don't go to a forum of volunteers

    where it is considered rude to think your requirement is more important than others.

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon

    Hi Stew,

    Thanks for your reply. The Table structure for all products are different.

    For example, the table Alpha_XYZ will have different columns than Beta_XYZ.

    The Source data is residing in Oracle, not MS Access.

    I want the Insert statement to work like "Insert into <Product>_<tablename> values ();" where I can keep the <Product> as a dynamically changing value.

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon


    Apologies , Dint mean it that way !!

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon

    Karthik, I am not so worried about getting the Values part, I have just written that as per the normal syntax. I wish to know how to change the table name dynamically.

    Am sorry if I could not make you understand the situation.

    Thanks Anyways

  • User_FRTCM
    User_FRTCM Member Posts: 116 Blue Ribbon

    Thanks for your suggestion Nimish, but its the codes also that written in VB which are getting migrated to PL SQL procedures.

    I want to append the tablenames dynamically while doing inserts, what thoughts you have about putting up dynamic sql here ?

    Thanks

  • Sergei Krasnoslobodtsev
    Sergei Krasnoslobodtsev MoscowMember Posts: 495 Silver Badge
    edited Mar 31, 2014 8:25AM

    It isn't absolutely clear that you want to make....

    with a certain probability it is possible to assume that it is better for you to use dynamic sql/plsql:

    create or replace procedure ins_dsql (p_tab_dest user_tables.TABLE_NAME%type,p_tab_src user_tables.TABLE_NAME%type)
    as
      v_cols_src varchar2(400);
      v_cols_dst varchar2(400);
      v_sql varchar2(2000);
    begin
    
         select
              listagg(u.COLUMN_NAME,',') within group (order by u.COLUMN_ID)
         into
             v_cols_src
         from user_tab_cols u where u.TABLE_NAME = p_tab_src;
         select
              listagg(u.COLUMN_NAME,',') within group (order by u.COLUMN_ID)
         into
             v_cols_dst
         from user_tab_cols u where u.TABLE_NAME = p_tab_src;
    
         v_sql := 'insert into '||p_tab_src||'('||v_cols_dst||')'||' select  '||v_cols_src||' from '||p_tab_src;
          dbms_output.put_line(v_sql);
    --      execute immediate ;
    end;
    
    
    User_WI23P
This discussion has been closed.