Hello
I have 7 Purchase Order Tables
tblPOCo1,
tblPOCo2,
to tblPOCo7
Each table has an identical structure.
I have another table tblJob. Each Purchase Order has a unique Job number from tblJob.
If I had just one Purchase Order table I could create an inner join between tblJob and tblPOCox.
How should I do the same but with 7 Purchase Order Tables (rows tblJob will have one matching PO in any 1 of the 7 PO tables) .
I know how to append all of the 7 tables into one using Union All but don't know if this is the right route and if it is what I should do next. If the answer lies with a temp table please descibe.
Many Thanks
It's never too late ;) Are you using stored procedures in your applications or straight SQL?
Anyway, as far as your question goes, UNION seems to work ok for you.
What do you do with the merged tables after the UNION? If you need to play with the resulting table a lot, you might want to use table variables like:
EXEC Utility.dbo.spFindProc 'table', 'Conference'
DECLARE @BigTable TABLE(tblPOCo INT, OtherField1 INT)
INSERT INTO @BigTable (tblPOCo, OtherField1)
SELECT tblPOCo1, OtherField1 FROM tblPOCox1
UNION
SELECT tblPOCo2, OtherField2 FROM tblPOCox2
...
OR:
INSERT INTO @BigTable (tblPOCo, OtherField1)
SELECT tblPOCo1, OtherField1 FROM tblPOCox1
INSERT INTO @BigTable (tblPOCo, OtherField1)
SELECT tblPOCo2, OtherField2 FROM tblPOCox2