Presumably the ID numbers allocated to Emp_ID in each, of the two, databases "overlap" and "clash"?
Winding the clock back it would have been better that one started at 100,000 and the other DB at 200,000 - but assuming that did not happen:
You could just "add" 100,000 (or whatever is "enough" to avoid any clash) to the Emp_ID from the database being imported to make them unique.
You could have to also add 100,000 to the Emp_ID in the Notes table, and so on.
You could also, perhaps?, make all existing IDENTITY "negative" on import. Personally I find having negative ID numbers is not idea - sometime people fail to see the "-" when they look at them ... and of course if they ACTUALLY appear on any End User reports that doesn't look very good!!
After you've done the Import you need to change the "next available IDENTITY number" on that table to be after the now-largest value (maybe, instead of next-sequential-number, you would set it to 200,000 so that it is clearly "after the merge")
Its not quite that straight forward. You can do
INSERT INTO MyTable
SELECT *
FROM MyRemoteServer.MyRemoteDatabase.dbo.MyTable
to insert all the rows from Remote into Local. But that won't work if you have an IDENTITY column in the table, instead you have to do
SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO MyTable
(
Complete list of ALL column names
)
SELECT *
FROM MyRemoteServer.MyRemoteDatabase.dbo.MyTable
SET IDENTITY_INSERT dbo.MyTable OFF
but that won't get you a change in your ID number, so you would have to do:
SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO MyTable
(
MyID, Complete list of ALL other column names
)
SELECT MyID + 100000 AS MyID
, Complete list of ALL other column names
FROM MyRemoteServer.MyRemoteDatabase.dbo.MyTable
SET IDENTITY_INSERT dbo.MyTable OFF
Other than that you just have to import the Employee table before the Notes table (so that the new Emp_ID exists before you try to insert the Notes rows).
Do you need to do this "again" or is it a one off?
Doing it "again", so that you accommodate "changes" is much more tricky. You have to delete anything no longer present in RemoteDatabase, add new rows, and update all the existing rows (or just the ones that have changed to speed up the process)
You need to do a Dress Rehearsal to prove that your code is correct, so you probably want to make a copy of your Target database (using BACKUP and RESTORE-to-new-TEMP-name), practice the IMPORT from RemoteDatabase until you have it correct (repeat the RESTORE before starting each new import-test run)
If performance, when referencing the database direct (using FROM MyRemoteServer.MyRemoteDatabase.dbo.MyTable) is bad, then take a BACKUP of that database, ON that server, COPY the backup file to Target server, and RESTORE to TEMP database on Target Server - and then you just need to use
FROM MyTempCopyDatabase.dbo.MyTable
instead