INSERT INTO and UNION

[LuckyJim1001]

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 ?

 

[SQLSister]

Maybe the duplicate is comeing from the second insert not the union which was inserted as part of the selct into? Whatever the second insert is doing may be adding more records that already exist (and why don't you have the proper unique constraints?)

"NOTHING is more important in a database than integrity." ESquared

 

[SQLBill]

My answer is - Yes. It is two different statements. First you are creating a table by using a SELECT INTO (with a Union). Then once that table is created and populated, you then run an INSERT INTO to add more data to the table. Depending on what your two SELECT queries do, you could return the same data and end up with duplicate values.

So in other words...that INSERT isn't part of the UNION statement.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[LuckyJim1001]

Thanks Bill.

My lack of T-SQL knowledge shining through.

Off to RTFM