Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Location
    San Francisco Bay Area
    Posts
    5

    Unhappy Unanswered: Please help a noob: combine two tables

    Hi there,

    I am a total noob. I just used SQL Loader to load two tables to Oracle via .csv files. Wow, that worked.

    I now want to combine the records in these tables into one consolidated table. I'm confused...seems to be several ways to do this.

    Pairs of columns, including one from each table, need to be consolidated into a new table that lists all of the records (See example, below). How can I make Table C from Table A and Table B?

    * Do I need to create a new "destination" table to put these into first, or is there a command I should to use that will create the destination table and merge the .csv files in one step?

    * Do I put the commands to create the new file in my .ctl file or refer to a separate file in my .bat file

    Thanks everyone.

    EXAMPLE:
    Table A
    COLOR SMELL
    ------ ----
    blue foul
    orange sweet


    Table B
    HUE ODOR
    --- --------
    red nasty
    yellow flowery


    Combo Table C
    COLOR_HUE SMELL_ODOR
    ----------- -------------
    blue foul
    orange sweet
    red nasty
    yellow flowery

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Numerous possible solutions exist
    1) Combine/append two *CSV file & load into new table
    2) use pure SQL to INSERT records from existing 2 tables into new table.
    It is up to you to decide how to get from here to there
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2011
    Location
    San Francisco Bay Area
    Posts
    5

    Question Noob help: combining tables with sql

    Hi there,

    Thanks for your response. So...I need to do this with SQL. One example I found on the web is:

    Code:
    Here is 2 Different Table,
    
    Table Name : Salary
    Name	Salary
    -----      ------
    emil	1000
    rayden	2000
    
    
    Table Name: Flight
    FligtTicket	Price
    ------------         -----
    Kuala Lumpur	265
    Bangkok	             878
    
    
    Below Sql is use to combine above 2 Tables Become below 1 Table
    
    SELECT ISNULL(a.Name,b.FlightTicket) AS Col1, ISNULL(a.Salary,b.Price) AS Col2 FROM Salary a
    FULL JOIN Flight b ON a.Name = b.FlightTicket
    
    Col1	             Col2
    -----                   -----
    emil	             1000
    rayden	             2000
    Kuala Lumpur	265
    Bangkok	             878
    However, I don't completely understand this person's example. I don't understand what is supposed to be 'a' and what is supposed to be 'b' in the script. What are they? One other issue is that I have nulls in my data and need to keep the nulls, so I don't know if this is a good script. Do I need to get rid of these ISNULL arguments? Finally, is the term "Flight" the name of the final table?

    Also, if there is a simpler script I can use, I am open to suggestions.Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What are they?
    They are table alias; such as below

    SELECT B.MGR, A.EMP_NAME FROM EMP A, EMP B
    WHERE A.MGR_ID = B.EMP_ID;
    understand this?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    u got to use an union in ur select instead of joins
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I suppose that the first option suggested by Anacedent would be the simplest, as you already have created SQL*Loader control file that loads data into different tables - you should just APPEND contents of the second file into the same table.

    Anyway, as you already have those values in two tables, the third one can be created using the CTAS (Create Table AS) technique:
    Code:
    SQL> select * from tab_a;
    
    COLOR                SMELL
    -------------------- --------------------
    blue                 foul
    orange               sweet
    
    SQL> select * from tab_b;
    
    HUE                  ODOR
    -------------------- --------------------
    red                  nasty
    yellow               flowery
    
    SQL> create table tab_c as
      2    select color color_hue,
      3           smell smell_odor
      4    from tab_a
      5    union all
      6    select hue,
      7           odor
      8    from tab_b;
    
    Table created.
    
    SQL> select * from tab_c;
    
    COLOR_HUE            SMELL_ODOR
    -------------------- --------------------
    blue                 foul
    orange               sweet
    red                  nasty
    yellow               flowery
    
    SQL>

  7. #7
    Join Date
    Mar 2011
    Location
    San Francisco Bay Area
    Posts
    5

    Smile Response from noob

    Thank you all so much for the valuable tips. I will try them.

    It is great that you experts take a little time from a busy schedule to help people. It is very important and I cannot thank you enough.

  8. #8
    Join Date
    Mar 2011
    Location
    San Francisco Bay Area
    Posts
    5

    Red face Noob help: combining tables with sql error

    Hi there--

    So I tried using the above example with my tables and received an error. Any insights from the resident experts? My code and the error are shown below

    Code:
    select * from PLANNING_BO_EDW
    select * from PLANNING_BO_SAP
    create table TAB_C as
    select RUN_DATE PERIOD,
    SKU_NUMBER SKU
    from PLANNING_BO_EDW
    union all
    select PERIOD,
    MATERIAL
    from PLANNING_BO_SAP;
    ORA-00933: SQL command not properly ended
    00933. 00000 - "SQL command not properly ended"
    *Cause:
    *Action:
    Error at Line: 1 Column: 29

  9. #9
    Join Date
    Mar 2011
    Location
    San Francisco Bay Area
    Posts
    5

    Help a noob create a table error resolved

    Hi there--

    FYI I dropped the first two select statements from the above and executed only the remainder of the script and it ran OK so far. No error.

    Thanks for your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •