Solved

Merge two tables

Posted on 2004-03-24
5
769 Views
Last Modified: 2008-03-03
I have 2 tables in 2 separate databases called Authors with the following fields:
-AuthId(Identity Field)
-Fname
-Lname

The tables contain book authors from all over the world. One of the tables is North America and the other table is authors from rest of the world. There are many Authors with the same FName and Lname. The only way they are identified is by the Author. Now I want to merge this two tables with a Stored Procedure.

How do I do This??
0
Comment
Question by:AutomaticSlim
[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
5 Comments
 
LVL 2

Expert Comment

by:dhenson
ID: 10673118
Just for clarification....

Your not looking for a sql to join the two tables together in one result set, but rather a Stored Procedure to create an additional table (dropping if exists) that has all the records from both original tables?

Is that correct?
0
 
LVL 2

Expert Comment

by:dhenson
ID: 10673122
Also....

Did the seeds for the two tables overlap so that the AuthID's would not necessarily be unique?

dhenson
0
 

Author Comment

by:AutomaticSlim
ID: 10673307
Here is an Example:

Authtable1
Id     Fname      LName
1      James       Robertson
2      Mark         Jackson
3      Janet        Ciega


Authtable2
Id     Fname      LName
1      Adam       Pictch
2      Will          Steiner
3      Will          Steiner
4      James       Robertson

Note that James Roberston in Table2 is not same person as James Robertson in Table1.
Now I want to add Authtable2 to Authtable1


Thanks
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 50 total points
ID: 10680586
This should do it:

INSERT INTO Authtable1 (FName, LName)
SELECT FName, LName
FROM OtherdbName.dbo.Authtable2


If you want, add a column to the original table for the old AuthId so you can link back to the old table:

ALTER TABLE AuthTable1
ADD OldAuthId INT

Then:

INSERT INTO Authtable1 (FName, LName, OldAuthId)
SELECT Fname, LName, AuthId
FROM OtherdbName.dbo.Authtable2
0
 

Author Comment

by:AutomaticSlim
ID: 10689910
what you suggested above works but now the relationships between my other tables gets unstable and some other issues that I didn't think of.
I guess I asked a question without thinking about it properly.........

Thanks for the help
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a View from a CTE 15 50
Access PS SQLSERVER from powershell 1 30
SQL: get ride of blank rows 11 24
SQL: Transformation or Pivot 3 37
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

710 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