SQL & PL/SQL :: Dynamic Insert From Multiple Tables?
May 29, 2013
We get data from our customers which we load into temporary tables.The goal is to consolidate this data into one single table.
Following are the rules:
1) final table should have all the columns from all the tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)
4) There could be 'N' number of tables
Following is the most realistic data.
1) T1/T2/T3 has the sample data which cover most of our test cases
2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.
3) we might have more than 3 tables in our production environment, so the query should work for N tables.
4) I have given the explanation of how each row should be derived to be inserted in T4
5) the only information we have to work with is the TABLE_NAME(s) and its metadata from USER_TAB_COLUMNS
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;
[code].....
Explanation for each row:
row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match)
row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match)
row3) This row comes from T1 and T3
row4) This row comes from T2 and T3
row5) This row comes from T3
I am trying to insert records in multiple tables. I know how to view data using joinig, but unable to understand how to insert records in multiple tables using Joining. I searched it on net, but didn't find much. I have also tried to write a code, but it is not working, I have seen some examples on different websites where people are using SELECT in INSERT statement for joining. What is the correct Syntax to INSERT record in Multiple tables.
Insert into library_users, library_users_info (library_users.username, library_users.password, library_users_info.address, library_users_info.phone_no) VALUES (...)
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below. ------------------------------------------- Create or replace procedure p_proc as spidm spriden.spriden_pidm%type; cursor mycur is select * from mytable; begin for rec in mycur [code]...... ----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
I trying to create a procedure that could update one or more different table into clob data - but only those tables that contain your search (indexed) word.
I have a 3 tables: item, common_lookup, member
> CREATE INDEX i_item_barcode ON item (item_barcode) INDEXTYPE IS ctxsys.context; CREATE INDEX i_item_title ON item (item_title) INDEXTYPE IS ctxsys.context; CREATE INDEX i_item_subtitle ON item (item_subtitle) INDEXTYPE IS ctxsys.context; CREATE INDEX i_common_lookup_meaning ON common_lookup (common_lookup_meaning) INDEXTYPE IS ctxsys.context; CREATE INDEX i_account_number ON member (account_number) INDEXTYPE IS ctxsys.context;
[Code]....
Now is the question.. If it possible to create the procedure search_execute which can add data depending on subbmited parameters ? If it possible, can You give me an example (dbms_output/update_clob), for search_execute to recive something like that:
> search_execute ('Wars', item, item_title) > >
select * from clob data : 1 1068 ASIN: B00003CX5P 1014 Star Wars - Episode I The Phantom Menace (CLOB) (BLOB) PG MPAA 22-mar-2005 3 16-lut-2012 17:31:55 3 16-lut-2012 17:31:55 2 1069 ASIN: B00006HBUJ 1014 Star Wars - Episode II Attack of the Clones (CLOB) (BLOB) PG MPAA 22-mar-2005 3 16-lut-
[Code]....
I try write something like this (it is not compile - and I know where is mistake, I tried to figure it out in several different ways - This is just my way of thinking expression) : > CREATE OR REPLACE PROCEDURE search_execute (v_text IN VARCHAR2, v_column_name IN VARCHAR2, v_table_name IN VARCHAR2) IS v_clob CLOB; v_tmp_clob CLOB; CURSOR c1 IS (SELECT * FROM v_table_name WHERE contains (v_column_name, v_text) > 0); CURSOR c2 IS (SELECT column_name FROM user_tab_cols WHERE table_name = v_table_name); BEGIN
I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...
Why I'm getting the error "ORA-00947: not enough values"?
INSERT ALL INTO mtl_system_items_interface VALUES (process_flag, transaction_type, set_process_id, [code].......
Im facing some issues with my form, getting stuck... prob desc below : I have Table A which has columns; car_year, car_type, line_num, line_text. Table B has country, car_year, car_type, line_num, data_entry_amt.
Table A contains data which gets updated once every year only. Contains what year model is the car, what type of car it is.. Line_num has numbers starting from 1 which indicates the different part number and line_text has description for that line_num. eg : 2010 Toyota 1 Windshield 2010 Toyota 2 Door 2010 Toyota 3 Tire 2010 BMW 1 Windshield 2010 BMW 2 Door 2010 BMW 3 Tire 2010 BMW 4 Rear_mirror
Table B contains specific data related to table A, Contains country where car details n prices are, car_year, car_type, line_num, and amount($) for that part. for example : Australia 2010 Toyota 1 400.50 Australia 2010 Toyota 2 200.40 Australia 2010 Toyota 3 308.25
So in year 2010, in Australia, Toyota's Door was sold at $200.40 Now, Table A will have similar data for this year and users will enter data for table B throughout this year. I tried master-detail form for this but it doesnt work. Because every year line_num change in table A and therefore cant implement a fixed number or rows on the form for amount for table B.
How to use dynamic listing but im not familiar with it. So how i should go about doing this. My form has structure has below :
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
i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
the code works "fine" here but the xml in the fb_xml table is not EXECUTED it simply escaped by oracle , i know i should add something so that the XML have to execute and generate.
We have multiple environments and our dev and UAT ones are now different from staging and live (I know, but I am not in a position to get this fixed). I have a set of updates that need to go through to live and in some cases they reference rows that do not exist in the UAT environment, and yet they have to (rigid, dumb process) go through that environment.
Basically, the insert I need to do takes info from two tables and does an insert into a third. That target table has a not null constraint on the affected fields, so the insert fails, quite rightly.
There's lots of info available on how to do conditional inserts with single sub-queries, using DUAL and EXISTS (or rather NOT EXISTS, but that's easy to swap), but those don't seem to easily translate for this one.
The sql that works when everything exists is:
insert into wmcontent.wm_manda_corpserv_companies (wm_manda_company_code, wm_corp_company_code) values ( (select wm_company_code from wmcontent.wm_m_and_a_company where wm_company = 'SW'), (select min(oid) from wmcontent.wx_category where content_type = 2 and name = 'SW') );
In desperation I even tried using "log errors reject limit unlimited" but, no doubt due to my misunderstanding of how that works, I ended up getting the error "ORA-06550: line 38, column 1: PL/SQL: ORA-00972: identifier is too long" as a result.
Insert multiple record in table. I have a table of customers . It has column cus_name, cus_fruit, cus_date, cus_qty.
Select * from customers; cus_name cus_fruit cus_date cus_qty Maria Anders Apple 18-July-2013 10 Maria Anders Apricot 18-July-2013 20 Maria Anders Asparagus 18-July-2013 100 Maria Anders Avocado 18-July-2013 5 Ana Trujillo Apple 18-July-2013 10 Ana Trujillo Apricot 18-July-2013 20 Ana Trujillo Asparagus 18-July-2013 100 Ana Trujillo Avocado 18-July-2013 5
how I can insert record in one time in table. All table data same only change the cus_name.
Thomas Hardy Apple 18-July-2013 10 Thomas Hardy Apricot 18-July-2013 20 Thomas Hardy Asparagus 18-July-2013 100 Thomas Hardy Avocado 18-July-2013 5
After Insert record result. Select * from customers;
cus_name cus_fruit cus_date cus_qty Maria Anders Apple 18-July-2013 10 Maria Anders Apricot 18-July-2013 20 Maria Anders Asparagus 18-July-2013 100 Maria Anders Avocado 18-July-2013 5 Ana Trujillo Apple 18-July-2013 10 Ana Trujillo Apricot 18-July-2013 20 Ana Trujillo Asparagus 18-July-2013 100 Ana Trujillo Avocado 18-July-2013 5 Thomas Hardy Apple 18-July-2013 10 Thomas Hardy Apricot 18-July-2013 20 Thomas Hardy Asparagus 18-July-2013 100 Thomas Hardy Avocado 18-July-2013 5
I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where A.client_id = B.client_id and time_id between 20090101 and 20091001 and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101 Issue DateDATE Expected_return_dateDATE Original_return_date- Fine - Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101 Issue DateDATE Expected_return_dateDATE Original_return_date DATE Fine 2 Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
I have 2 object tables. Location and a department. Department references a location Object. But this wont insert. I get "0 rows created".
CREATE OR REPLACE TYPE location_objtyp AS OBJECT ( locationID NUMBER, name VARCHAR2(48) ); / CREATE OR REPLACE TYPE dept_objtyp AS OBJECT ( deptID NUMBER, name VARCHAR2(48), locationID_ref REF location_objtyp ); [code]...
This does not insert. I get no error. Only - "0 rows created".
i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
I have a form which has three detail portions. I want that when I press SAVE, it should insert data in two tables & then run the specific code & then insert data in other two tables.
I am using Developer 6i. Couldn't find out the proper trigger or related thing.
I have a temp table and want to insert that data into 2 tables. In both of my tables i have a sequence column but in my second table its a foreign key to the main table
up_query="INSERT INTO AFFILIATE (FNAME,LNAME,EMAIL,PHONE,ORG,ADDRESS1,ADDRESS2,COUNTY_ID,AFF_ID) Select a.FNAME, a.LNAME,a.EMAIL,a.PHONE, a.ORG, a.ADDRESS1, a.ADDRESS2,C.COUNTY_ID,'AF'||aff_seq.nextval FROM temp_aff A LEFT OUTER JOIN COUNTY C ON UPPER(A.COUNTY)=C.CNAME"; $up_query2= "INSERT INTO TEST4 (DEPT_ID,CAMP_ID,AFF_ID, PASSWD) SELECT D.DEPT_ID,C.CAMP_ID,aff_seq.currval,'Zq'||dbms_random.string('A',4)||'$8' from dual,TEMP_AFF A left outer join DEPT D ON (UPPER(A.DEPT)=UPPER(D.DNAME)) left outer join CAMPUS C ON (UPPER(A.CAMPUS)=UPPER(C.CPNAME))";
However in my test4 table its inserting the aff_seq.currval for all records. Its not incrementing as in the affiliate table.
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
I have a primary table and some secondary tables. I need to use the key from Primary table and get the sum/totals from the other tables with a single query.
For example, I have 3 tables employee, employeesal and employeerewards in which employee is the primary table. I need to get the total salary for an employee from employeesal table and total rewards from the employeerewards table using one query.
I am pasting the test data below:
create table employee (eno number(9), ename varchar2(40)); insert into employee values(1,'Jack'); insert into employee values(2,'Jim');
[Code]....
I am looking for a better way of doing this in terms of performance. In my projecct, those 3 tables are very big tables and I doubt if I do two inner queries and then use a outer query may not be the best way to do it.
Is there any better way to write this query in terms of performance?
What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.
If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.
I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.
Let me know if I can further clarify.
I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:
SELECT l.sku AS "SKU", l.loc AS "LOC", l.qty AS "QTY", o.ncikey AS "NCI", r.description AS "NCI DESC", o.qtyexpected AS "NCI QTY EXP", o.qtyreceived AS "NCI QTY REC", o.loc AS "NCI LOC", o.status
