Bulk load failed

Msg 5301, Level 16, State 1, Line 1

Bulk load failed. User does not have ALTER TABLE permission on table 'Table_2'. ALTER TABLE permission is required on the target table of a bulk load if the target table contains triggers or check constraints, but the  'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified. ALTER TABLE permission is also required if the 'KEEPIDENTITY' bulk hint is specified.

Issue:

While we perform Bulk insert operation on table it will through error as requires bulkadmin and alter table permission for user

When you use bulk insert on main table it ignores constraints and disables triggers.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms188365(v=sql.90)?redirectedfrom=MSDN

Permissions:

Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required if one or more of the following is true:

·         Constraints exist and the CHECK_CONSTRAINTS option is not specified

·         Triggers exist and the FIRE_TRIGGER option is not specified.

·         You use the KEEPIDENTITY option to import identity value from data file.

https://www.red-gate.com/simple-talk/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/

• By default, the BULK INSERT statement ignores constraints and disables triggers.
• By default, the INSERT…INTO statement executes check constraints and triggers.

Fix for this issue:

We can create temp table first and use this temp table in bulk insert operation then we can get the row from temp table will resolve disable triggers and permission requirement on user.

   bulk insert Table_2 from  'D:\test.csv' with ( fieldterminator = ',', rowterminator='\n')

create table #temp(tid int,tname varchar(50))

bulk insert #temp from  'D:\test.csv' with ( fieldterminator = ',', rowterminator='\n')

insert into Table_2

select * from #temp