Try this:

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

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;