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