this:

if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))

drop table BigTable; --Drop SOURCE

if exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable '))

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

insert into RefTable(software_name_raw,software_name_amended)

select

bt.software_name_raw,

'Needs Updating'

from

BigTable bt

where

not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);

select * from RefTable;

update bt set

software_name_raw = rt.software_name_amended

from

BigTable bt

inner join RefTable rt

on (bt.software_name_raw = rt.software_name_raw)

where

rt.software_name_amended <> 'Needs Updating';

select * from BigTable;

GO

if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))

drop table BigTable; --Drop SOURCE

if exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable '))

drop table RefTable; --Drop Lookup

GO