Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    20

    Unanswered: INSERT INTO and UNION

    Hi all,

    Not a problem more of a 'why does it do that'

    I have a script that does this:

    SELECT
    some stuff
    INTO
    new table
    FROM
    source
    WHERE
    filters
    UNION
    Another Select etc here

    INSERT INTO
    same table as above
    SELECT
    From, where etc

    Now the question.

    The UNION is not doing it's distinct thing on the last INSERT INTO. I know this because I have 2 exact same records in the resulting table

    No bad thing, this is exactly what I wanted it to do.

    Excuse my ignorance but is the INSERT INTO ignoring what happens before it ?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes of course.
    Why would you expect a statement to honour the distinct nature of a UNION if it is not a UNION itself?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    I'm a little confused here...

    The union is different from UNION ALL, IMHO, it should ignore the duplicates.
    However, the insert-statement after the select...into.. is just another statement. It is oblivious of the statement before.

    does this compare to what you're talking about?
    Code:
    use monkey
    go
    set nocount on
    go
    
    -- create the tables
    create table table1 (mycolumn int)
    create table table2 (mycolumn int)
    go
    
    -- insert some data with duplicate rows
    insert into table1 values (1)
    insert into table1 values (1)
    insert into table1 values (2)
    
    -- insert with a union
    insert into table2 (mycolumn)
    (select mycolumn from table1
     union 
     select mycolumn from table1
    )
    go
    
    /*
    mycolumn
    -----------
    1
    1
    2
    */
    select * from table1
    
    /*
    mycolumn
    -----------
    1
    2
    */
    select * from table2
    
    -- and here goes the second insert...
    insert into table2 (mycolumn)
    (select mycolumn from table1 where mycolumn = 1)
    
    select * from table2
    
    /*
    
    mycolumn
    -----------
    1   --> union-statement
    2   --> union-statement
    1   --> second insert-statement
    1   --> second insert-statement
    */
    go
    drop table table1
    drop table table2
    go

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by LuckyJim1001 View Post
    Excuse my ignorance but is the INSERT INTO ignoring what happens before it ?
    SQL isn't a "state aware" language. SQL doesn't know what you did yesterday, or what you will do tomorrow.

    The UNION operator basically "welds together" two separate SELECT operations so that they produce a single result set, which often leads people to assume that they somehow become a single statement. That isn't the case, at least from a logical perspective each SELECT gets executed separately then their result sets are co-mingled.

    In your example above, there are three separate SELECT statements. Two of them have a UNION, and one is wholly independant.

    Does that help or just confuse things?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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