Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Select from UNION into table

    What am I missing?

    I have three tables "UNIONED" and I want the this inserted into a table.

    INSERT INTO mytable (A, B, C, D, E)
    SELECT A, B, C, D, E
    FROM
    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E
    FROM a_lot_of_parts)
    GROUP BY A,B,C,D,E

    This part alone works just like I want it:

    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E
    FROM a_lot_of_parts)

    I just want it inserted inte stated columns in my table.

    I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by oneleg
    What am I missing?

    I have three tables "UNIONED" and I want the this inserted into a table.

    INSERT INTO mytable (A, B, C, D, E)
    SELECT A, B, C, D, E
    FROM
    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E
    FROM a_lot_of_parts)
    GROUP BY A,B,C,D,E

    This part alone works just like I want it:

    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E
    FROM a_lot_of_parts)

    I just want it inserted inte stated columns in my table.

    I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...
    Hi

    Does

    Code:
      SELECT A, B, C, D, E
    FROM
    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E
    FROM a_lot_of_parts)
    GROUP BY 1, 2, 3, 4, 5
    work? You col titles come from the first table in you UNION (i.e. temp_Parts1). Personally, I would avoid using * in unions - they can hide errors that are dead obvious if you explicitely state the column names (e.g. non-union compatible tables).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It would be MUCH better of you told us what you are actually tryin to do. The example isn't a very good one...UNION removes dups for example, and The GROUP BY could be replaced with a DISTINCT. Also you make no mention of constraints.

    Post the DDL of your tables, some sample data, and expected results.

    Like this (YOu'll notice that the result sets are the same):

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(A int, B int, C int, D int, E int)
    CREATE TABLE temp_PARTS1(A int, B int, C int, D int, E int)
    CREATE TABLE temp_PARTS2(A int, B int, C int, D int, E int)
    CREATE TABLE a_lot_of_parts(A int, B int, C int, D int, E int)
    GO
    
    INSERT INTO temp_PARTS1(A,B,C,D,E)
    SELECT 1,2,3,4,5 UNION ALL
    SELECT 6,7,8,9,0
    
    INSERT INTO temp_PARTS2(A,B,C,D,E)
    SELECT 11,12,13,14,15 UNION ALL
    SELECT 16,17,18,19,20
    
    INSERT INTO temp_PARTS1(A,B,C,D,E)
    SELECT 1,2,3,4,5 UNION ALL
    SELECT 6,7,8,9,0 UNION ALL
    SELECT 1,2,3,4,5 UNION ALL
    SELECT 6,7,8,9,0 UNION ALL
    SELECT 1,2,3,4,5 UNION ALL
    SELECT 6,7,8,9,0 UNION ALL
    SELECT 1,2,3,4,5 UNION ALL
    SELECT 6,7,8,9,0 UNION ALL
    SELECT 1,2,3,4,5 UNION ALL
    SELECT 6,7,8,9,0
    GO
    
    
    INSERT INTO mytable99 (A, B, C, D, E)
         SELECT A, B, C, D, E
           FROM ( SELECT * 
    		FROM (
    		       SELECT * 
    			 FROM temp_PARTS1 
    			UNION 
    		       SELECT * 
    			 FROM temp_PARTS2
    		      ) AS XXX
                   UNION
    	      SELECT A, B, C, D, E
    		FROM a_lot_of_parts
    	    GROUP BY A,B,C,D,E) AS YYY
    
    SELECT * FROM myTable99
    GO
    
    TRUNCATE TABLE myTable99
    GO
    
    
    INSERT INTO mytable99 (A, B, C, D, E)
         SELECT A, B, C, D, E FROM temp_PARTS1 UNION
         SELECT A, B, C, D, E FROM temp_PARTS2 UNION
         SELECT DISTINCT A, B, C, D, E FROM a_lot_of_parts
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99, temp_PARTS1, temp_PARTS2, a_lot_of_parts
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Posts
    50
    That din't work either... but I found out that this works:

    INSERT INTO mytable (A, B, C, D, E)
    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E FROM a_lot_of_parts
    GROUP BY A, B, C, D, E)


    What's the alternative to using UNION in this case?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by oneleg
    That din't work either... but I found out that this works:

    INSERT INTO mytable (A, B, C, D, E)
    (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
    UNION
    (SELECT A, B, C, D, E FROM a_lot_of_parts
    GROUP BY A, B, C, D, E)


    What's the alternative to using UNION in this case?
    Whatdya mean it doesn't work...did you cut and paste the whole thing?

    UNION. FYI, removes duplicates....

    And I'm still at a loss as to what you're really trying to accomplish....or is this just an exercise?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Posts
    50
    "That didn't work either... " was my response to pootle flump's post...

    Yes it's an exercise... an excercise in trying to understand how the French people who designed this Access database thought... I'm moving it to SQL-Server and redesigning it at the same time.

    These are the tables involved in this section I'm redesigning.

    1. List of parts that are to be priced in i system.

    2. Table "a-lot-of-parts" that contains parts that "should be quoted". This may or may not contain all the parts that are to be priced. These table contains a list of parts from certain factories.

    3. List of parts to be priced that are found in another system.

    4. List of parts to be priced that are NOT found in another system.

    It seems like they want to combine these three tables (2,3,4) into one, avoiding dulicates.

    The tables used are very big and the data can be considered "secrets" so I'd rather not post any of that..

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by oneleg
    "an excercise in trying to understand how the French people who designed this Access database thought...
    Are you trying to piss me off?

    French people....

    Keep your freinds close, but your enemies closer...hmmm wonder which category they fit in?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    They fall into the "Mostly harmless" category.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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