SQL 2005 Query copy between two tables

Posted on 2008-11-16
Last Modified: 2012-05-05
I have 2 tables that I need to do some copying with:
table hrdata will update table Customers
I need to copy some fields and combine 2 fields into 1 field
hrdata.Ansattnr will update Customers.CustomerID
hrdata.FirstName + hrdata.LastName will update Customers.ContactName
hrdata.Title will update Customers.ContactTitle

If hrdata.Ansattnr is not in Customers.CustomerID then it will be added as new record in Customers
If hrdata.Ansattnr is in Customers.CustomerID then it will be updated in Customers

Any help doing this as SQL script in SQL Server 2005 is highly appreciated.
Question by:Tom
    1 Comment
    LVL 26

    Accepted Solution

    -- update the records that are exixts in customers
    SET C.ContactName = H.FirstName + ' ' + H.LastName ,
          C.ContactTitle = H.Title
    FROM hrdata H INNER JOIN Customers C ON  H.Ansattnr = C.CustomerID

    -- Insert the records that are not exists
    INSERT INTO Customers (CustomerID, ContactName, ContactTitle)
    SELECT H.Ansattnr, H.FirstName + ' ' + H.LastName, H.Title
    FROM hrdata H
    WHERE NOT EXISTS (SELECT CustomerID FROM Customers  C WHERE H.Ansattnr = C.CustomerID)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now