Joining multiple tables and insert into new table

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)

Joining multiple tables and insert into new table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-15 : 07:43:44

Dipti writes "Hi! Team,

I have 7 tables called BenefitsData1998, BenefitsData1999, BenefitsData2000, BenefitsData2001, BenefitsData2002, BenefitsData2003 and BenefitsData2004. Each representing the year.

All the tables have same column names except for those with the year.
for eg. Table BenefitsData1998 has column names as
Year, SSN, FullName(L,F), BirthDate, HireDate, TermDate1998, Hours1998, More_than_1000(either 1 if > 1000 hours, or else 0)

for eg. Table BenefitsData1999 has column names as
Year, SSN, FullName(L,F), BirthDate, HireDate, TermDate1999, Hours1999, More_than_1000(either 1 if hours worked > 1000 hours, or else 0)
and so on till year 2004 same for all tables except for the years.

Problem is I tried doing subqueries, union, corelated queries, joins to get the data out from all these tables and insert it into a new table called BenefitsData, which has column names as

SSN, FullName(L,F), BirthDate, HireDate, TermDate1998, TermDate1999,
TermDate2000, TermDate2001, TermDate2002, TermDate2003, TermDate2004, Hours1998, Hours1999, Hours2000, Hours2001, Hours2002, Hours2003, Hours2004, TotalVestedYears.

with data as:
001-78-2431, Smith Bob, 8/30/1970, 4/17/1997, null, null, null, null, 7/25/2002, 8/30/2003, null, 250, 1127, 1225,1450,700,650,0,3(total 3 vested years as for 3 years he has hours > 1000)

But it gives me duplicate records. So if you can help me writing a select statement to get data from all these tables and to insert it into a new table with data that are pulled from the respective years table to their respective columns and has to be unique.
for eg. my new table BenefitsData should have data as shown above."

Kristen
Test

22859 Posts

Posted - 2005-07-15 : 08:15:04

I reckon the easiest way, of several available!, would be to INSERT into BenefitsData a SELECT * FROM BenefitsData1998 [i.e. the first table] and then for each other database BenefitsData1999, BenefitsData2000, ...

1) UPDATE any existing rows to add the new column data specific to the table you are wanting to import (i.e. TermDate1999, TermDate2000, ...)

2) INSERT rows that did not already exist (these will then get the extra column data from subsequent tables in Step (1) for those tables when processed in turn

Kristen

ddesai
Starting Member

1 Post

Posted - 2005-07-19 : 15:04:59

Hi! Kristen,

Thanks for your big help, I really appreciate it. I'll try doing the way you recommended to and will let you know the results are.

Thankyou again for the suggestion, it's a big help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources