Populating a table from multiple sources

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Populating a table from multiple sources

Author

Topic

paul.wilky27
Starting Member

3 Posts

Posted - 2004-07-06 : 06:41:19

Hi,

I'm trying to populate one table with data from a dozen sources using a single query.

The problem is that not all the fields in the tables are common. For example, field A in table A might not exist in table B and visa-versa.

Any help is appreciated,
Thanks,
Paul.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-07-06 : 06:56:32

Have you identified your Primary/Foreign Keys? Come to think about it, could you give an exmaple of what you're trying to do? Some CREATE TABLE statments, maybe some sample data if you as well.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-06 : 06:56:41

Look up UNION...

paul.wilky27
Starting Member

3 Posts

Posted - 2004-07-06 : 07:05:27

Ok, here is one of 9 queries I have at the moment:

INSERT INTO prospects_all ( company, address1, address2, posttown, county, postcode, dxno, tel, fax, initials, itpositi, itsal, itsurnam, ittitle, litinit, litposit, litsal, litsurn, littitle, piinitia, pisal, pisurnam, pititle, probinit, probposi, probsal, probsurn, probtitl, propinit, propposi, propsal, propsurn, proptitl, sal, surname, title, Staff, shoinit, email, shoposit, shosal, shosurn, shotitle, itinitia )
SELECT AREA1P.company, AREA1P.address1, AREA1P.address2, AREA1P.posttown, AREA1P.county, AREA1P.postcode, AREA1P.dxno, AREA1P.tel, AREA1P.fax, AREA1P.initials, AREA1P.itpositi, AREA1P.itsal, AREA1P.itsurnam, AREA1P.ittitle, AREA1P.litinit, AREA1P.litposit, AREA1P.litsal, AREA1P.litsurn, AREA1P.littitle, AREA1P.piinitia, AREA1P.pisal, AREA1P.pisurnam, AREA1P.pititle, AREA1P.probinit, AREA1P.probposi, AREA1P.probsal, AREA1P.probsurn, AREA1P.probtitl, AREA1P.propinit, AREA1P.propposi, AREA1P.propsal, AREA1P.propsurn, AREA1P.proptitl, AREA1P.sal, AREA1P.surname, AREA1P.title, AREA1P.staff, AREA1P.shoinit, AREA1P.email, AREA1P.shoposit, AREA1P.shosal, AREA1P.shosurn, AREA1P.shotitle, AREA1P.itinitia
FROM AREA1P
WHERE (((Len([AREA1P].[company]))>0));

This query populates prospect_all, and there 8 other similar queries which do the same only with slighty different fields and data.

What i am trying to do is populate prospects_all with something like:

SELECT table1.*, table2.*, table3.* INTO prospect_all
FROM tabel1, table2, table3

How this sheds more light.

Thanks again.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-07-06 : 07:12:46

Yup... RickD has already given you the answer to your question.

To keep this simple, if AREA1P has the following columns :
AREA1P.COL1, AREA1P.COL2, AREA1P.COL3

and AREA2P has :
AREA2P.COL1, AREA2P.COL2

you want to insert both sets of data in to one table using ONE query. If that's the case then you should try something like :


INSERT INTO prospects_all(COLUMN1, COLUMN2, COLUMN3)
select AREA1P.COL1, AREA1P.COL2, AREA1P.COL3
from AREA1P
union 
select AREA2P.COL1, AREA2P.COL2, null

etc..

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

paul.wilky27
Starting Member

3 Posts

Posted - 2004-07-06 : 07:18:17

great, ive never used a union select but i'll give it a whirl.

thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources