Buildings
RecordID  BuildingName  BuildingDescription  ManagerName
--------  ------------  -------------------  -----------
1         Orian         This is the Orian    Montgomery
2         Stellar       This is Stellar      Johnson

New Tables:

Buildings
RecordID  BuildingName  BuildingDescription  ManagerID
--------  ------------  -------------------  -----------
1         Orian         This is the Orian    101
2         Stellar       This is Stellar      102

Managers
ManagerID  ManagerName 
---------  -----------
101        Montgomery
102        Johnson

CREATE TABLE #Managers (ManagerID int identity(1,1), ManagerName varchar(40))
CREATE TABLE #BuildingsOld (RecordID int,BuildingName varchar(100),BuildingDescription varchar(100),ManagerName varchar(40))
CREATE TABLE #BuildingsNew (RecordID int,BuildingName varchar(100),BuildingDescription varchar(100),ManagerID int)

INSERT INTO #BuildingsOld(RecordID,BuildingName,BuildingDescription,ManagerName)
SELECT 1,'Orian','This is the Orian','Montgomery' UNION ALL
SELECT 2,'Stellar','This is Stellar','Johnson'

INSERT INTO #Managers (ManagerName)
SELECT DISTINCT ManagerName FROM #BuildingsOld

INSERT INTO #BuildingsNew (RecordID,BuildingName,BuildingDescription,ManagerID)
SELECT A.RecordID,A.BuildingName,A.BuildingDescription,B.ManagerID 
FROM #BuildingsOld A INNER JOIN #Managers B ON A.ManagerName = B.ManagerName

SELECT * FROM #BuildingsNew
SELECT * FROM #Managers

DROP TABLE #Managers
DROP TABLE #BuildingsOld
DROP TABLE #BuildingsNew