SQL Insert INTO - adding non existing IDs

Question

I am trying to clean up the data in a table so that I can create a 1 to 1 relationship. The table with the primary key has more records than the table it shares the 1 to 1 relationship with. To solve this problem I am trying to insert the missing ID's from table 2 into table 2.

INSERT INTO medical_Surveillance(dbo.Medical_surveillance.EmpID)
SELECT dbo.EmployeeInformation.EmpID
FROM EmployeeInformation
WHERE not exists (select dbo.medical_Surveillance.EmpID from medical_Surveillance
WHERE dbo.medical_Surveillance.EmpID = dbo.EmployeeInformation.EmpID)

What is wrong with this SQL statement? When ran it tries to insert values into columns other than dbo.Medical_surveillance.EmpID.

Answer 1

You can do the same thing with a left join:

INSERT INTO medical_Surveillance(EmpID)
SELECT EmployeeInformation.EmpID
FROM EmployeeInformation
    LEFT JOIN medical_surveillance ON EmployeeInformation.EmpID = medical_surveillance.EmpID
WHERE medical_surveillance.EmpID IS NULL

Answer 2

Resolved, Nothing was wrong with the SQL statement. I had other columns that were set to not null, but had no default values. When the statement ran it would try to insert null values into those columns. Fixxed by adding default values.

Answer 3

INSERT INTO medical_Surveillance(EmpID)
SELECT ei.EmpID
FROM EmployeeInformation as ei
WHERE ei.EmpID not in (select ms.EmpID from medical_Surveillance as ms)

Answer 4

You can also use Merge Into now, (not back when this question was asked). You can read about it here.

It is much more efficient than writing an insert, update and delete statement yourself. It is also possible to use conditions, still. Check here.