Based on the description the two rows containing
kti001
kti001
are not duplicates. Otherwise the SELECT statement would return both of those.
In SQL if you want to eliminate duplicate rows you need to specify
DISTINCT
in your query. Since you haven't used that keyword, duplicates are allowed.
Having that said the only option is that there is a difference between the rows, perhaps a trailing space or something.
To find out the differences have a try with something like this on your dynamic data
select '>' + empid + '<' from table
That should help you to visualize possible spaces within the data.
[Edit]
As an example that using an IN operator does not remove duplicates, consider the following statements:
Create the test table
CREATE TABLE TestTable1 (
EmpId VARCHAR(MAX)
);
Add initial rows
INSERT INTO TestTable1 (EmpId) VALUES ('kti001');
INSERT INTO TestTable1 (EmpId) VALUES ('kti001');
INSERT INTO TestTable1 (EmpId) VALUES ('kti002');
Select without DISTINCT
SELECT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')
Result
EmpId
------
kti001
kti001
kti002
Select with DISTINCT
SELECT DISTINCT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')
Result
EmpId
------
kti001 <-- duplicate removed
kti002
Add a non duplicate row with extra space in the beginning
INSERT INTO TestTable1 (EmpId) VALUES (' kti001');
Select
SELECT EmpId FROM TestTable1 WHERE EmpID in ('kti001','kti001','kti002')
Results, the newly added row isn't listed since it does not satisfy the condition
EmpId
------
kti001
kti001
kti002
List the content of the table
SELECT '>' + EmpId + '<' FROM TestTable1
Result
(No column name)
----------------
>kti001<
>kti001<
>kti002<
> kti001<
Its impossible to say if an extra space is the problem or something else but the main point is that duplicates aren't removed from the result set without DISTINCT keyword.