?
Solved

Merging two tables

Posted on 2005-04-21
12
Medium Priority
?
282 Views
Last Modified: 2010-03-19
Hi there,

I have two tables. One table has city, state, zip. The other table has city, state, population.

Many records in the two table has the same city and state. I would like to merger the two tables. For each 2 records that has the same city and state, I would like to eliminate 1 of them but keep all related date city, state, zip and population.

And the records that are not duplicate can be kept. So the final table will have 3 groups of records
1. Has city, state, zip, no population
2. Has city, state, no zip, population
3. Has city, state, zip, population

Is there a SQL script (or ASP script) that I can run to do the above job?
Thanks.
Trailerman
0
Comment
Question by:trailerman
12 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1200 total points
ID: 13838695

SELECT ISNULL(z.city, p.city) AS city,
    ISNULL(z.state, p.state) AS state,
    MIN(z.zip) AS zip,
    MIN(p.population) AS population
FROM zips z
FULL OUTER JOIN populations p ON p.city = z.city AND p.state = z.state
GROUP BY ISNULL(z.city, p.city),
    ISNULL(z.state, p.state)
0
 

Author Comment

by:trailerman
ID: 13838860
I have tried the script and got an error

Invalid object name 'zips'.

(line 5)
0
 

Author Comment

by:trailerman
ID: 13838968
After I removed zips (line 5) ans populations (line 6), the script works. It gives me the result table.

How can I create a new table instead of just viewing the result?

Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13838969
On behalf of ScottPletcher, you have to change the table names he used to the actual name of your table.  You have to change ZIPS and POPULATIONS tables to the name of your tables.
0
 

Author Comment

by:trailerman
ID: 13838979
No, actually I figure it out. It's not that.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13838983
Are you getting the same error or something else?
0
 

Author Comment

by:trailerman
ID: 13839006
no, it's working after I removed the zips and populations (keep the table name only -line 5 and 6).

But I want to create a actual final table using the create table command, not just view it using the select command as it is right now.
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 280 total points
ID: 13839041
Using the original statement, to create the final table all you need is to add an INTO to this:

SELECT ISNULL(z.city, p.city) AS city,
    ISNULL(z.state, p.state) AS state,
    MIN(z.zip) AS zip,
    MIN(p.population) AS population
INTO YourFinalTable
FROM zips z
FULL OUTER JOIN populations p ON p.city = z.city AND p.state = z.state
GROUP BY ISNULL(z.city, p.city),
    ISNULL(z.state, p.state)

Hope this helps.
0
 
LVL 6

Expert Comment

by:PePi
ID: 13839058
CREATE TABLE NewTable
(
     City nvarchar(50),
     State nvarchar(50),
     Zip nvarchar(20),
     Pop int
)

INSERT INTO NewTable
SELECT ISNULL(z.city, p.city) AS city,
    ISNULL(z.state, p.state) AS state,
    MIN(z.zip) AS zip,
    MIN(p.population) AS population
FROM zips z
FULL OUTER JOIN populations p ON p.city = z.city AND p.state = z.state
GROUP BY ISNULL(z.city, p.city),
    ISNULL(z.state, p.state)
0
 

Author Comment

by:trailerman
ID: 13839071
Hi rafrancisco,

It works. Though now I realize, after I created the new table, it doesn't have primary key field (auto-number). How do I add that field in?

Thanks
0
 

Author Comment

by:trailerman
ID: 13839098
never mind. I figure it out.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13839100
You can try this:

ALTER TABLE YourTable ADD [ID] INT IDENTITY PRIMARY KEY

Or like what PePi suggested, you can try creating the table first:

CREATE TABLE NewTable
(
     ID int identity primary key,
     City nvarchar(50),
     State nvarchar(50),
     Zip nvarchar(20),
     Pop int
)

INSERT INTO NewTable (city, state, zip, pop)
SELECT ISNULL(z.city, p.city) AS city,
    ISNULL(z.state, p.state) AS state,
    MIN(z.zip) AS zip,
    MIN(p.population) AS population
FROM zips z
FULL OUTER JOIN populations p ON p.city = z.city AND p.state = z.state
GROUP BY ISNULL(z.city, p.city),
    ISNULL(z.state, p.state)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 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