INSERT fails with a subset of columns when columns are NULL

Hennie1234 — Wed, 21 Jun 2023 08:37:00 GMT

Hi,

I think it is a bug when I try to insert data with INSERT statement in a table in Synapse data warehouse

insert titles (title_id, title, type, pub_id, notes) values('PC9999', 'Net Etiquette',
'popular_comp', '1389', 'A must-read for computer conferencing.')
GO

This will give an error

Msg 15806, Level 0, State 1
Cannot insert the value NULL into column 'ytd_sales', table 'titles'; column does not allow nulls. INSERT fails.

DROP TABLE IF EXISTS titles;

CREATE TABLE titles
(
title_id varchar(80),
title varchar(80) NOT NULL,
type char(12) NOT NULL,
pub_id char(4) NULL,
price DECIMAL(7,2) NULL,
advance DECIMAL(7,2) NULL,
royalty int NULL,
ytd_sales int NULL,
notes varchar(200) NULL,
pubdate datetime2(0) NOT NULL
);

Re: INSERT fails with a subset of columns when columns are NULL

HimanshuS-msft — Thu, 22 Jun 2023 00:01:03 GMT

Hello Hennie1234

Thanks for using the Fabric community .

I think I have a repro and I think the error message with to be corrected .

The correct message should be
Error message : Cannot insert the value NULL into column 'pubdate ', table 'titles'; column does not allow nulls. INSERT fails.

I am woerking with the internal team to get this fixed .

As I workaround in your case either update the below
pubdate datetime2(0) NULL

OR
pass the pubdate in the INSERT statement .

Thanks
HImanshu

topic INSERT fails with a subset of columns when columns are NULL in Data Warehouse

INSERT fails with a subset of columns when columns are NULL

Re: INSERT fails with a subset of columns when columns are NULL