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.
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.
- 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
