CODEWITHSUNDEEP
sqlsql-server
Gillardo
Gillardo

Reputation: 9828

Cannot insert the value NULL into column XXX. INSERT fails. Insert valid only

I get the above message when inserting multiple records into a table. Fair enough that the data is invalid, and it cannot be inserted. But how can i tell SQL to insert all the valid ones.

Currently when i get this error, the whole insert fails. But i would like SQL to not insert the invalid rows, but insert all the valid rows.

I am using syntax similar to this

INSERT INTO myNewTable (columnName1, columnName2, columnName3)
    SELECT myValue1, myValue2, myValue3
    FROM myOldTable

I cannot change the SQL to exclude values in the WHERE clause because this database uses an ETA like structure table, but if this table is incorrectly configured, then this is when my insert fails.

I am wondering if there is a settings or statement that i can call first before the insert, much like when you set IDENTITY_INSERT to be ON, or something

Upvotes: 0

Views: 185

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can create an instead of trigger on your target table that filters out the invalid rows.

create table dbo.T(ID int not null);

go

create trigger dbo.TT on dbo.T 
instead of insert as
begin
  insert into T(ID)
  select ID
  from inserted
  where ID is not null;
end

go

insert into dbo.T values(1),(null);

select * from T;

Result:

ID
-----------
1

Upvotes: 1

CodeNewbie
CodeNewbie

Reputation: 2091

Assuming one of myValue1, myValue2, myValue3 is actually NULL, and there is a NOT NULL constraint on some columns of the table myNewTable, you can simply choose those values where the constraint is not violated using a WHERE clause.

INSERT INTO myNewTable (columnName1, columnName2, columnName3)
SELECT myValue1, myValue2, myValue3
FROM myOldTable
WHERE myValue1 IS NOT NULL; --myValue2 and myValue3 can also be added 
                            --with OR if your table structure has those columns 
                            --defined to be NOT NULL too

Here, instead of worrying about getting only the valid values inserted and the invalid ones dropped, you are just avoiding the invalid values altogether. This is a better solution rather than finding a way to ignore constraint violations.

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try to this

INSERT INTO myNewTable (columnName1, columnName2, columnName3)
    SELECT myValue1, myValue2, myValue3
    FROM myOldTable
    where myValue1 is not null 
          and myValue2 is not null 
          and myValue3 is not null

Upvotes: 0

Related Questions