Hello,

I have to say that while the procedure described leads to correct result, it seems to be lacking certain ingenuity so often found in the articles here. In my opinion, this is how one would do the task without taking too much time to think about various possibilities. Nothing wrong with that, if you just need to do it once, but I wouldn't publish it in an article.

To be more precise, the article does several actions where there could be just one (e.g., insert all, delete those that are in first table, delete those that are in second table). Why not insert just those unused outright in the first place?

When filling the table with data, various loops and even import from Excel are mentioned - but not a hint that there is way to do it in set-based SQL.

The article does not contain INSERT INTO statements, that would enable readers to create the same environment easily. Also, it just mentions how to find available keys, not how to apply the result and get new ID's assigned correctly.

This is how I would go about removing duplicates in a similar case (for simplicity, I used temporary tables instead of your permanent tables in 2 databases). You will observe, that I have used a table "numbers" which is not defined in the script. This is a one-column table, also known as "Tally" table, and you can find a lot about it and its uses here on the forums (also how to create and fill it, therefore I won't repeat that, for the sake of simplicity).

IF object_id('tempdb..#emp_one') IS NOT NULL DROP TABLE #emp_one

IF object_id('tempdb..#emp_two') IS NOT NULL DROP TABLE #emp_two

IF object_id('tempdb..#emp_free_no') IS NOT NULL DROP TABLE #emp_free_no

IF object_id('tempdb..#duplicates') IS NOT NULL DROP TABLE #duplicates

GO

/*create test tables*/

CREATE TABLE #emp_one(emp_no VARCHAR(4))

CREATE TABLE #emp_two(emp_no VARCHAR(4))

/*insert sample data*/

INSERT INTO #emp_one

SELECT '0001'

UNION

SELECT '0003'

UNION

SELECT '0007'

UNION

SELECT '0008'

UNION

SELECT '0009'

INSERT INTO #emp_two

SELECT '0001'

UNION

SELECT '0004'

UNION

SELECT '0005'

UNION

SELECT '0006'

UNION

SELECT '0007'

/*prepare table emp_two for renumbering - add column that will hold the new employee number*/

ALTER TABLE #emp_two ADD new_emp_no VARCHAR(4)

GO

/*table of available (unused) employee numbers*/

CREATE TABLE #emp_free_no(id INT IDENTITY, emp_no VARCHAR(4))

INSERT INTO #emp_free_no (emp_no)

SELECT Q.emp_no

FROM

(SELECT RIGHT('000'+ CAST(number as VARCHAR(4)),4) as emp_no

FROM numbers

WHERE number > 0 and number < 10000) as Q

LEFT JOIN #emp_one e1 ON e1.emp_no=Q.emp_no

LEFT JOIN #emp_two e2 ON e2.emp_no=Q.emp_no

WHERE e1.emp_no IS NULL AND e2.emp_no IS NULL

/*table of employee numbers that need to be replaced - exist in both tables

these numbers will remain in table emp_one and will be changed in table emp_two*/

CREATE TABLE #duplicates (dupid INT IDENTITY, emp_no VARCHAR(4))

INSERT INTO #duplicates

SELECT e2.emp_no

FROM #emp_two e2

JOIN #emp_one e1 ON e1.emp_no=e2.emp_no

/*write the new employee number*/

UPDATE e2

SET new_emp_no=ISNULL(f.emp_no,e2.emp_no)

FROM #emp_two e2

LEFT JOIN #duplicates d ON d.emp_no = e2.emp_no

LEFT JOIN #emp_free_no f ON f.id=d.dupid

/*show result*/

SELECT * FROM #emp_two

You can now move the rows from table emp_two to table emp_one, and use new_emp_no as key when inserting into table emp_one.