Dedicated SQL Pool, cannot insert NULL

Question

I have one table in SQL Server on premise that I wanted to migrate to Azure Dedicated SQL Pool.

CREATE TABLE test1
(
    col1 INT PRIMARY KEY,
    col2 INT NULL
);

CREATE TABLE test2_dedicated_pool
(
   col1 INT NOT NULL,
   col2 INT NULL
) 
WITH 
(
   DISTRIBUTION = HASH(col1),
   CLUSTERED COLUMNSTORE INDEX
);

I setup a pipeline with a copy task to do the simple copy (with staging) but it fails with the following error:

SQL DW Copy Command operation failed with the error 'Cannot insert the value NULL into column 'col2', table 'Distribution_57.dbo.Table_someguid_57', column does not allow nulls.

Anyone know what this error is?

that looks like an user error. Check your table, maybe script as create and check the definition. You will probably find the reason or which column that is about. — Onur Omer, Jun 27 '22 at 15:11

score 0 · Answer 1 · answered Jun 28 '22 at 11:54

Error - Cannot insert the value NULL into column

The above error indicates that there is not null constraint on col2.

Use the command below to view schema of “test2_dedicated_pool” table.

SELECT * from sys.schemas where name = “test2_dedicated_pool”

You may find not null constraint on “col2”. Then just try to alter schema and remove not null constraint.

score 0 · Answer 2 · answered Jul 11 '23 at 13:01

This has been driving me crazy for the last hour.

I had

CREATE TABLE [dbo].[Client](
    [ClientKey] [BIGINT] NOT NULL,
    [LoadedDate] [DATETIME] NOT NULL,
    [UpdatedDate] [DATETIME] NULL
 PRIMARY KEY NONCLUSTERED  ([ClientKey]) NOT ENFORCED
)
  WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (ClientKey)
);

And I was getting the same error, that it could not insert a NULL value into UpdatedDate because it does not allow NULLs.

Solution: I added a comma at the end of that last row before PRIMARY KEY... and then I stopped getting that error.

CREATE TABLE [dbo].[Client](
    [ClientKey] [BIGINT] NOT NULL,
    [LoadedDate] [DATETIME] NOT NULL,
    [UpdatedDate] [DATETIME] NULL,
 PRIMARY KEY NONCLUSTERED  ([ClientKey]) NOT ENFORCED
)
  WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (ClientKey)
);

Dedicated SQL Pool, cannot insert NULL

2 Answers2