I have one table importedOrders which includes the varchar Fields "FirstName" and "LastName" that indicates the employee(!) that is responsible. I also have a table importedEmployees With a EmployeeID, FirstName and LastName. I'm migrating some data from the importedTables into some normalized tables. The table Orderline will need to have an employeeID Foreign key(primary key is in a new Employee table) and in my migration-query, I need to find the right employeeID based on the FirstName and LastName. I got the following:
INSERT INTO Orderline (ProductID, Quantity, .... , EmployeeID) SELECT ...., (SELECT EmployeeID FROM dblab2.importEmployee E WHERE importOrders.FirstName = E.FirstName AND importOrders.LastName = E.LastName)
Is this right(can't test it right now) or do I need to join the tables(which kind of join?)? Am I even close? Would appreciate all help I can get.