INSERT INTO TargetTable
SELECT SourceTable.Col1 AS ColA,
       SourceTable.Col2 AS ColB,
       'Some text' AS ColC
FROM SourceTable
LEFT JOIN TargetTable
  ON SourceTable.ReferenceValue = TargetTable.ReferenceValue
WHERE TargetTable.ReferenceValue IS NULL

For this example the target table must have at least three fields. Additional fields must be nullable.