I am performing bulk insert on a table in sql server 2012, at the same time i am picking the last inserted row with max() function and inserting it into another table , how to perform this when my table is getting data from multiple sources because while performing insertion into secondary table there is time delay while insertions are still happening in primary table so next time max() will pick up last updated row and i will loose some rows which are not max() but still inserted into primary table meanwhile.

create table dbo.emp
id int primary key identity(1,1),
emp_id int,
name varchar(255),
address varchar(255)
)create table dbo.empx
id int primary key,
emp_id int foreign key references dbo.emp(id),
    declare @temp int ;
        set @temp=1;
        while @temp<1000
        insert into dbo.emp(emp_id,name,address)values (100+@temp,'Ename'+LTRIM(STR(@temp)),'123 Sample Address'+LTRIM(STR(@temp)));
        set @temp=@temp+1;         insert into dbo.empx  select max(dbo.emp.id),max(dbo.emp.emp_id) from dbo.emp         end
Question author Siddharth-bhardwaj | Source



Use OUTPUT Clause...

CREATE TABLE #empx(Id INT ,emp_id VARCHAR(50))DECLARE @temp INT ;SET @temp=1;    WHILE @temp<1000    BEGIN        INSERT INTO dbo.emp(emp_id,name,address)            OUTPUT INSERTED.Id,INSERTED.emp_id INTO #empx(Id,emp_id)        VALUES (100+@temp,'Ename'+LTRIM(STR(@temp)),'123 Sample Address'+LTRIM(STR(@temp)));        SET @temp=@temp+1;     ENDINSERT INTO dbo.empx(Id,emp_id)SELECT Id,emp_id FROM #empx

Or Use a trigger

CREATE TRIGGER EmpLog   ON  dbo.emp   AFTER InsertAS BEGIN    SET NOCOUNT ON;    Insert into dbo.empx (id,emp_id) Select id,emp_id from inserted;ENDGO
Answer author Unnikrishnan-r

Ask about this question here!