I have a table Called EmployeeInfo which has two column namely Employee and EmpID. EmpID is the Primary Key, INT NOT NULL but NOT IDENTITY, Employee is Varchar (50).
I have a staging table in which i have the same two columns. Now, using SSIS, i have to select the data from staging table and apply lookup, whether the Employee in staging table exists in EmployeeInfo table or not. If not then i have to insert those non-matched
records into EmployeeInfo table. But, as the column EmpID of EmployeeInfo table is NOT IDENTITY, i have to insert the Max+1 value in EmpID column.
Example: If the Max EmpID is 20 then the next row should have the ID 21. Next to that would be 22, and so on.
But, in ssis pipeline, there could me thousands or row entering in the table at the same time, so how would i be able to get Max+1 value for each row,
NOTE: My manger has asked me, not to create a new EmpID IDENTITY column and replace the present EmpID column.
View Complete Post