Solved

Append Multiple Tables to Temp Table

Posted on 2006-07-11
8
448 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:doddwell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17082150
Hi doddwell,

Why do you have 7 PO tables?

Regards,

Patrick
0
 

Author Comment

by:doddwell
ID: 17082246
We are an umbrella company.  'Inside' the umbrella there are 7 separate companies.  I thought about putting all of the POs into one table when I designed the system but thought that this would not give me consequtive PO numbers for each company.  In retrospect perhaps I should have looked into this further and found a way of creating PO numbers with a suffix.  But it's too late now!
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 200 total points
ID: 17082872
>>But it's too late now!
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
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:doddwell
ID: 17082972
I'm usong Stored Procedures.  How would you have achieved unique company specific consequtive numbers within the same table?

I have managed to merge the contents of all of the tables using a view (vwAllPOs) which uses Union All.  I then execute an SP which is a Select of vwAllPOs Inner Joined to another table.  This works for me - so now I'm even more confused about temporary tables and their benefit.

I use an Access Data Project as the front end.  With my solution above I get to see all the records.  In your last example you suggest a temp table....how do I see the records at the front end if I use a temporary table (I know how to create them and get records in there but don't know how to get data back out!)

Many Thanks

0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17083258
Adding a CompanyId to your Tables and making CompanyID+POId (or whatever id) a Unique Index.

To view Temp Variable tables back just use it like a regular table like:
SELECT tblPOCo, OtherField1 FROM @BigTable

Now. I'm not recommending you use temp table variables since I don't know what you need to do with the result.
0
 
LVL 35

Expert Comment

by:James0628
ID: 17089221
You'd want to have the company ID, or something equivalent, in your single PO table so that you could identify which PO's were for which company (assuming that you don't already have that).  To generate a separate sequence of PO numbers for each company, you'd just need to have a separate next (or last or current) PO number stored somewhere for each company.  For example, if you had a table with company info (ID, name, etc.), you might put the field there.  Then the software that assigns the PO numbers would just use the appropriate field for each company.

 The concept is simple enough.  How easy it would be to implement will depend on the specifics of your situation.

 James
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17089474
something like this  will give you consequtive PO numbers for each company

select
      case when tblPOCo1.ponumber is not null then tblPOCo1.ponumber
      case when tblPOCo2.ponumber is not null then tblPOCo2.ponumber
      case when tblPOCo3.ponumber is not null then tblPOCo3.ponumber
      case when tblPOCo4.ponumber is not null then tblPOCo4.ponumber
      case when tblPOCo5.ponumber is not null then tblPOCo5.ponumber
      case when tblPOCo6.ponumber is not null then tblPOCo6.ponumber
      case when tblPOCo7.ponumber is not null then tblPOCo7.ponumber
      end ,
      tbljob.column1,
      tbljob.column2
from
      tbljob
      inner join tblpocox
      on
      tbljob.column1 = tblpocox.column1
      
      left outer join tblPOCo1
      on tblpocol.PO  =  tbljob.po
      left outer join tblPOCo1
      on tblpoco2.PO  =  tbljob.po
      left outer join tblPOCo1
      on tblpoco3.PO  =  tbljob.po
      left outer join tblPOCo1
      on tblpoco4.PO  =  tbljob.po
      left outer join tblPOCo1
      on tblpoco5.PO  =  tbljob.po
      left outer join tblPOCo1
      on tblpoco6.PO  =  tbljob.po
      left outer join tblPOCo1
      on tblpoco7.PO  =  tbljob.po
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17089479
/*slight syntax error in last post use this one*/

select
      case when tblPOCo1.ponumber is not null then tblPOCo1.ponumber
       when tblPOCo2.ponumber is not null then tblPOCo2.ponumber
       when tblPOCo3.ponumber is not null then tblPOCo3.ponumber
       when tblPOCo4.ponumber is not null then tblPOCo4.ponumber
       when tblPOCo5.ponumber is not null then tblPOCo5.ponumber
       when tblPOCo6.ponumber is not null then tblPOCo6.ponumber
       when tblPOCo7.ponumber is not null then tblPOCo7.ponumber
      end ,
      tbljob.column1,
      tbljob.column2
from
      tbljob
      inner join tblpocox
      on
      tbljob.column1 = tblpocox.column1
      
      left outer join tblPOCo1
      on tblpocol.PO  =  tbljob.po
      left outer join tblPOCo2
      on tblpoco2.PO  =  tbljob.po
      left outer join tblPOCo3
      on tblpoco3.PO  =  tbljob.po
      left outer join tblPOCo4
      on tblpoco4.PO  =  tbljob.po
      left outer join tblPOCo5
      on tblpoco5.PO  =  tbljob.po
      left outer join tblPOCo6
      on tblpoco6.PO  =  tbljob.po
      left outer join tblPOCo7
      on tblpoco7.PO  =  tbljob.po
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question