INSERT INTO and UNION?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to combine 10 tables into an 11th table. All have the same same structure

This part works
INSERT INTO tblFullCatalog SELECT * FROM tblP2 WHERE (((tblP2.Prop_Row_1) Not Like ""))

and this part works
SELECT tblP1.*, tblP1.Prop_Row_1 FROM tblP1 WHERE (((tblP1.Prop_Row_1) Not Like "")
UNIO
SELECT tblP2.*, tblP2.Prop_Row_1 FROM tblP2 WHERE (((tblP2.Prop_Row_1) Not Like ""))
UNIO
SELECT tblP3.*, tblP3.Prop_Row_1 FROM tblP3 WHERE (((tblP3.Prop_Row_1) Not Like "")); [for all 10

but how should they be combined? I also need to empty tblFullCatalog before new records are added

thanks in advance
 
Hi,


INSERT INTO FullCatalog
SELECT a.*
FROM (
SELECT P1.*, P1.Prop_Row_1 FROM P1 WHERE P1.Prop_Row_1 Not Like ""
UNION
SELECT P2.*, P2.Prop_Row_1 FROM P2 WHERE P2.Prop_Row_1 Not Like ""
UNION
SELECT P3.*, P3.Prop_Row_1 FROM P3 WHERE P3.Prop_Row_1 Not Like ""
) As a



should do, in Access 2000 or later.



Robert said:
I need to combine 10 tables into an 11th table. All have the same same structure.

This part works:
INSERT INTO tblFullCatalog SELECT * FROM tblP2 WHERE
Click to expand...
(((tblP2.Prop_Row_1) Not Like ""));
and this part works:
SELECT tblP1.*, tblP1.Prop_Row_1 FROM tblP1 WHERE (((tblP1.Prop_Row_1) Not Like ""))
UNION
SELECT tblP2.*, tblP2.Prop_Row_1 FROM tblP2 WHERE (((tblP2.Prop_Row_1) Not Like ""))
UNION
SELECT tblP3.*, tblP3.Prop_Row_1 FROM tblP3 WHERE (((tblP3.Prop_Row_1) Not Like "")); [for all 10]

but how should they be combined? I also need to empty tblFullCatalog before new records are added.

thanks in advance
Click to expand...
 
Back
Top