Handling bulk insert on a table with multiple input sources in SQL

Question

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

        begin

        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

Answer 1

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; 

    END
INSERT INTO dbo.empx(Id,emp_id)
SELECT Id,emp_id FROM #empx

Or Use a trigger

CREATE TRIGGER EmpLog
   ON  dbo.emp
   AFTER Insert
AS 
BEGIN
    SET NOCOUNT ON;
    Insert into dbo.empx (id,emp_id) Select id,emp_id from inserted;
END
GO

Handling bulk insert on a table with multiple input sources in SQL

Sign up