SQL Server – How to Merge Two Tables

mergesql serversql-server-2008-r2t-sql

In SQL Server 2008 R2 I have a master table called Employees that holds about 20 fields. I have a second spreadsheet that has all the fields except 5 called UpdateToEmployees that I need to merge with Employees but only where the values are not the same.

What is the most efficient way to do this? My thought process (which may be the long way) is to:

Run A Select statement to get the rows where the values are not the same
Take that result set and insert into a temp table flagging a field exists with a yes/no value
If the field exists contains a yes value – run an update query
If the field exists contains a no value – run an insert query

Using the DDL below – how could I join on empID and update the records where when matched = Yes or True (whatever it is that it returns) and insert the records that currently do not exist in Employees

Create Table Employees
(
  empid varchar(50)
  ,empaddress varchar(500)
  ,phone varchar(12)
  ,emcontact1 varchar(500)
  ,emcontact2 varchar(500)
  ,active varchar(10)
  ,manager varchar(50)
  ,paygrade int
)

Insert Into Employees (empid,empaddress,phone,emcontact1, emcontact2, active, manager, paygrade) VALUES
('aa11', '123 blue', '1234567890', 'A A', 'B B', 'Yes', 'Manager 1', 1)
,('bb22', '123 green', '4567890000', 'B B', 'A A', 'Yes', 'Manager 2', 1)
,('cc33', '123 red', '789123456', 'E E', 'R R', 'Yes', 'Manager 3', 2)

Create Table UpdateToEmployees
(
  empid varchar(50)
  ,empaddress varchar(500)
  ,phone varchar(12)
)

Insert Into UpdateToEmployees (empid, empaddress, phone) VALUES
('aa11', '611 pink', '9999999999')
,('cc33', '212 orange', '0000000000')
,('ee99', '1411 red', '4444444444')
,('zz22', '2212 fox', '5555555555')
,('tt83', '1999 prince', '7777777777')

Best Answer

UNTESTED
as I do not have access to SQL Server at the moment, but try this, it should work, or at least point you in the right syntax direction for a Merge statement

MERGE Employees emp
USING UpdateToEmployees ute
ON [emp].[empid] = [ute].[empid]
WHEN MATCHED THEN
  UPDATE
  SET [emp].[empaddress] = [ute].[empaddress]
  ,[emp].[phone] = [ute].[phone]
WHEN NOT MATCHED BY TARGET THEN
INSERT (empid, empaddress, phone)
VALUES ([ute].[empid], [ute].[empaddress], [ute].[phone]);

Related Solutions

Sql-server – Merge two records in the same table, keeping foreign key relationships intact for both

This is a very straight-forward data cleanup task. Your solution is good (I'd recommend using a transaction construct as below, just to be safe) -- I've taken the same approach except I used set-based operations so it can be scaled up and automated once you come up with a better duplicate detection algorithm than the rudimentary one I provided.

/* Test setup */
CREATE TABLE [dbo].[Sites]
(
    Id int NOT NULL PRIMARY KEY,
    Name nvarchar(50) NULL,
    Path nvarchar(100) NULL
);

CREATE TABLE [dbo].[Logs]
(
    Id int NOT NULL PRIMARY KEY,
    SiteId int NOT NULL FOREIGN KEY REFERENCES [dbo].[Sites](Id),
    LogData nvarchar(MAX) NULL
);

INSERT INTO [dbo].[Sites](Id, Name, Path)
    VALUES
        (1, N'Google', N'http://www.google.com'),
        (2, N'Microsoft', N'http://www.microsoft.com'),
        (3, N'Yahoo', NULL),
        (4, NULL, N'http://www.yahoo.com');

INSERT INTO [dbo].[Logs](Id, SiteId)
    VALUES (1, 1), (2, 1), (3, 2), (4, 3), (5, 3), (6, 4);


/* Identify potential duplicates (note: very rough) */
SELECT
    s1.Id AS SourceId, s1.Name AS SourceName, s1.Path AS SourcePath,
    s2.Id AS DuplId, s2.Name AS DuplName, s2.Path AS DuplPath
    FROM [dbo].[Sites] s1
    INNER JOIN [dbo].[Sites] s2 ON
        (s2.Path LIKE (N'%' + s1.Name + N'%')) AND
        (s2.Id > s1.Id);


/* Merge duplicates */
DECLARE @duplicates table
(
    SourceId int NOT NULL,
    TargetId int NOT NULL,
    PRIMARY KEY (SourceId, TargetId),
    CHECK (SourceId != TargetId)
);

INSERT INTO @duplicates(SourceId, TargetId)
    VALUES ; /* Edit me! */


SET XACT_ABORT ON;

BEGIN TRANSACTION;

    UPDATE l
        SET SiteId = d.TargetId
        FROM @duplicates d
        INNER JOIN [dbo].[Logs] l ON l.SiteId = d.SourceId;

    UPDATE st
        SET
            Name = COALESCE(ss.Name, st.Name),
            Path = COALESCE(ss.Path, st.Path)
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] ss ON ss.Id = d.SourceId
        INNER JOIN [dbo].[Sites] st ON st.Id = d.TargetId;

    DELETE s
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] s ON s.Id = d.SourceId;

COMMIT TRANSACTION;


/* Validate the site attributes were merged correctly */
SELECT
    s.*
    FROM @duplicates d
    INNER JOIN [dbo].[Sites] s ON s.Id = d.TargetId;

Sql-server – Merge two rows into one row

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a 
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price

Related Question