Need to combine 2 tables into 1 uniquely-rowed table - help

Is this how it's done?

INSERT INTO student (
(SELECT DISTINCT a.*
FROM student_spring a, student_summer b
WHERE a.unique_key != b.unique_key)\
UNION
(SELECT DISTINCT b.*
FROM student_summer b, student_spring a
WHERE b.unique_key != a.unique_key)
)

I'm trying this but I keep losing data whenever I run this!

Also, how do I populate a database table from an XML file?

Thanx
Phil

Re: Need to combine 2 tables into 1 uniquely-rowed table - help

phillip.s.powell@gmail.com wrote:
> Is this how it's done?
>
> INSERT INTO student (
> (SELECT DISTINCT a.*
> FROM student_spring a, student_summer b
> WHERE a.unique_key != b.unique_key)\
> UNION
> (SELECT DISTINCT b.*
> FROM student_summer b, student_spring a
> WHERE b.unique_key != a.unique_key)
> )
>
> I'm trying this but I keep losing data whenever I run this!
>
> Also, how do I populate a database table from an XML file?
>
> Thanx
> Phil
>


I am guessing that student_summer and student_spring tables are
identical and you want to get a distinct list of students from both
summer and spring.

one way...

select distinct c.* from -- gets distinct students.
(
-- Give me all of the students from summer
select distinct a.* from student_summer
union all
-- Now give me all of the students from spring
select distinct b.* from student_sprint
) c;

Re: Need to combine 2 tables into 1 uniquely-rowed table - help

noone wrote:
> phillip.s.powell@gmail.com wrote:
> > Is this how it's done?
> >
> > INSERT INTO student (
> > (SELECT DISTINCT a.*
> > FROM student_spring a, student_summer b
> > WHERE a.unique_key != b.unique_key)\
> > UNION
> > (SELECT DISTINCT b.*
> > FROM student_summer b, student_spring a
> > WHERE b.unique_key != a.unique_key)
> > )
> >
> > I'm trying this but I keep losing data whenever I run this!
> >
> > Also, how do I populate a database table from an XML file?
> >
> > Thanx
> > Phil
> >
>
>
> I am guessing that student_summer and student_spring tables are
> identical

[snip]

Sorry they weren't identical after all, there were some mysteriously
added new columns and changed datataypes, my query works now!

Now the next one doesn't :(

Phil

Re: Need to combine 2 tables into 1 uniquely-rowed table - help

wrote in message
news:1141178194.870493.166460@u72g2000cwu.googlegroups.com.. .
> Sorry they weren't identical after all, there were some mysteriously
> added new columns and changed datataypes, my query works now!

This is a good illustration of why it's not a good practice to use * in
queries. When the columns change in number or order, queries such as your
union get broken.

A similar thing happens with insert statements when you don't list the
fields to insert into.
For example:
insert into mytable values (1, 2, 3) -- what happens when someone adds a
fourth field to mytable?

Regards,
Bill K.