i wanted to use invoke SQLExecDirect to execute such a sentence:"select * from Table_A union select * from Table_B into Table_C", but error occured. it seems that "UNION" and "INTO" can not be used in one sentence at the same time. however, i want SQL to perform just as i request:"select * from Table_A union select * from Table_B into Table_C". by the way, Table_A and Table_B are of the same structure.
Thanks a lot.


zhasm@126.com
Posted on 2005-05-16 06:16:21 by zhihui
Why not just execute two seperate statements?

select * from Table_A into Table_C
select * from Table_B into Table_C

Would that not do the same thing?

Spara
Posted on 2005-05-16 08:06:21 by Sparafusile

i wanted to use invoke SQLExecDirect to execute such a sentence:"select * from Table_A union select * from Table_B into Table_C", but error occured. it seems that "UNION" and "INTO" can not be used in one sentence at the same time. however, i want SQL to perform just as i request:"select * from Table_A union select * from Table_B into Table_C". by the way, Table_A and Table_B are of the same structure.


What about:

create tableC as (select * from tableA union select * from tableB)

or:

select * from (select * from tableA union select * from tableB) into tableC

neither of these is tested  8)
Posted on 2005-05-17 07:36:56 by sluggy
thanks sluggy and Sparafusile. i feel very encouraged.

my god! Sparafusile's solution is rather surprising and inspiring. i surely will have a try.
but one thing i am still wondering: if there are same records in Table_A and Table_B, there will be

duplicate records in Table_C. so, how to delete the duplicate records?

i also will try sluggy's solution. "Union" is better than "union all" for me, because the formmer dose not save the same records into the destination table.
Posted on 2005-05-17 22:27:27 by zhihui
duplicate records in Table_C. so, how to delete the duplicate records?

Use a WHERE NOT IN (SELECT keyvalue FROM tableA)
Posted on 2005-05-18 08:13:56 by sluggy
1.? create tableC as (select * from tableA union select * from tableB)
? ? ?this is not correct.
2.? select *? into tableC from (select * from tableA union select * from tableB) is correct.
? ? ?and ,select instinct * into tableC from (select * from tableA union select * from tableB) is just what i need.
? ? ?2005-may-25: select* into tableC from (select * from tableA union select * from tableB) is ok, and instinct is not needed.
3.? select * from Table_A into Table_C
select * from Table_B into Table_C

? ? ?is not correct. for "into "can only be followed by new and empty table name.once the first sentence is excuted, the second is invalid because the pre-existance of the tablec.

thank you again!
best regards!



Posted on 2005-05-19 03:30:53 by zhihui