February 8, 2016 at 7:23 am
Is it possible to execute a BULK insert partially - insert all what can be inserted and disregard errors? In the example below no record is inserted into the target table because of constraint errors, but is there any flag or something so that records 1 and 2 would be inserted anyway? I tried XACT_ABORT but it's not what I am looking for.
if object_id('Target', 'u') is not null drop table Target
if object_id('Codelist', 'u') is not null drop table Codelist
if object_id('Source', 'u') is not null drop table Source
go
create table Codelist (
Id int not null,
constraint PK_Codelist primary key clustered (Id)
)
create table Target (
CodelistId int not null,
Value varchar(10) null,
constraint FK_Target_CodelistId foreign key (CodelistId) references Codelist (Id)
)
create table Source (
CodelistId int not null,
Value varchar(10)
)
go
insert into Codelist (Id)
select 1 union all
select 2 union all
select 3
insert into Source (CodelistId, Value)
select 1, 'val1' union all
select 2, 'val2' union all
select 4, 'val4' union all-- error
select 5, 'val5'-- error
go
insert into Target (CodelistId, Value)
select CodelistId, Value
from Source
go
select * from Source
select * from Target
February 8, 2016 at 8:42 am
No. SQL's rules are that data modification statements are atomic, they either complete entirely or fail entirely.
You'll need extra logic in the insert to ensure that SQL only tries to insert rows that are valid.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 2:50 pm
insert into Target (CodelistId, Value)
select S.CodelistId, S.Value
from Source S
WHERE EXISTS (select * from Codelist L
where L.ID = S.CodelistId
OR
insert into Target (CodelistId, Value)
select L.ID , S.Value
from Source S
INNER JOIN Codelist L ON L.ID = S.CodelistId
_____________
Code for TallyGenerator
February 16, 2016 at 11:34 am
As noted, you can't do the INSERT if a key violation occurs. That's why you have keys! 😉
Not sure what your application/purpose here is, but if it's some kind of ETL process, you could clone the production table without the constraints/keys and INSERT INTO that cloned table. You still won't be able to insert into the production table, but you can run checks more easily that way to find constraint violations before trying the insert.
Rich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply