alan_lynch (3/7/2013)

---

right Lynn I will try again.

I have had a look at your merge statement which has been useful the only thing is i have one error from the query below.

Please bare in mind it works fine as it is, but when I add a duplicate row to the Source(BigTable) for my test data-- like so ('Microsoft Office 2003 Professional'),

I get the error message

Msg 8672, Level 16, State 1, Line 9

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

in my production data I will have many many rows the same from the source table but they are to be unique in the lookuptable

drop table BigTable --Drop SOURCE

drop table RefTable --Drop Lookup

--Create a Source table

CREATE TABLE BigTable

(

software_name_raw VARCHAR(255)

)

GO

--Insert records into Source table

INSERT INTO BigTable

VALUES

('Microsoft Office 2003'),

('Microsoft 2003 Office'),

('Microsoft Office 2003 Professional'),

('Sun Microsystems')

GO

--Create LOOKUP table

CREATE TABLE RefTable

(

software_name_raw VARCHAR(255) PRIMARY KEY,

software_name_amended Varchar (255)

)

GO

--Insert records into Lookup table

INSERT INTO RefTable

VALUES

('Microsoft Office 2003', 'Office 2003'),

('Microsoft 2003 Office', 'Office 2003'),

('Microsoft Office 2003 Professional', 'Office 2003'),

('Adobe', 'Adobe Inc')

GO

SELECT * FROM BigTable as Source_TABLE

SELECT * FROM RefTable as Lookup_TABLE

GO

--Next I will use the MERGE SQL command to synchronize the target table

--with the refreshed data coming from the LOOKUP table.

--MERGE SQL statement – Part 2

--Synchronize the lookup table with

--refreshed data from source table

MERGE RefTable AS TARGET

USING BigTable AS SOURCE

ON (TARGET.software_name_raw = SOURCE.software_name_raw)

--When records are matched, update the records if there is any change

WHEN MATCHED AND TARGET.software_name_raw = SOURCE.software_name_raw THEN

UPDATE SET TARGET.software_name_raw = SOURCE.software_name_raw

--When no records are matched, insert the incoming records from source into the lookup table

WHEN NOT MATCHED BY TARGET THEN

INSERT (software_name_raw, software_name_amended)

VALUES (SOURCE.software_name_raw, 'Needs Updating')

--$action specifies a column of type nvarchar(10)

--in the OUTPUT clause that returns one of three

--values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’,

--according to the action that was performed on that row

OUTPUT $action,

DELETED.software_name_raw AS Lookupsoftware_name_raw,

DELETED.software_name_amended AS Lookupoftware_name_amended,

INSERTED.software_name_raw AS Sourcesoftware_name_raw,

INSERTED.software_name_amended AS Sourcesoftware_name_amended;

GO

select * from BigTable as source_table

select * from RefTable as lookuptable