SQL Server: Convert nulls into values when inserting fails - can I force this?

I am trying to add NOT NULL and a DEFAULT to an existing table.

To do this, I am using a transitional table to populate any NULL values.

Table1 has the NULL column, Table2 has the improved desig开发者_Python百科n.

CREATE TABLE table1 (
    CustomerID INT
  , CartID NULL);

CREATE TABLE table2 (
    CustomerID INT
  , CartID NOT NULL DEFAULT NEWID());

INSERT INTO table2 (CustomerID, CartID)
SELECT CustomerID, CartID = CASE CartID WHEN NULL THEN NEWID() ELSE CartID END
FROM table1;

I still get the "Cannot insert the value NULL into column" error, even though I am populating every NULL value with a new value in the SELECT statement.

How can I make this work?

In SQL, NULL is not equal to NULL (and not unequal too).

"Does a dog have Buddha-nature or not?"
"NULL"

This is why CASE var WHEN NULL THEN … will never succeed.

Use this:

CASE WHEN CartID IS NULL THEN NEWID() ELSE CartID END

or just this:

COALESCE(CartID, NEWID())

I vote for Qassnoi's answer, but instead of copying to a new table, I would usually:

1. Alter table to allow null,
2. Update table with new ids where null,
3. Alter table again with not null restriction and default.

This would only work if noone else was using the table, so Quassnoi's answer is preferable.

You can change the code to the following:

INSERT INTO table2 (CustomerID, CartID)
SELECT CustomerID, COALESCE(CartID, NEWID())
FROM table1;