T-SQL 'Cannot insert the value NULL' error with non-NULL values

Question

I'm migrating a Simple Membership database to Identity 2.0. I'm copying a CreateDate datetime NULL column to a CreateDate datetime NOT NULL column. I've examined all the records in the Membership.CreateDate column in the data source table to verify that they contain valid DateTimes. This error is returned:

Cannot insert the value NULL into column 'CreateDate', table 'Settlement.dbo.AspNetUsers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I've also tried deleting all the records in Membership but one (its CreateDate column contains 2012-12-27 01:35:03.610). I get the same error.

I'm running the script in SSMS.

Migration script excerpts:

CREATE TABLE [dbo].[AspNetUsers] (
    [Id] [nvarchar](60) NOT NULL,
    [UserName] [nvarchar](15) NOT NULL,
    [AcId]  INT          NOT NULL,
    [LocId] INT          NOT NULL,
    [CreateDate] [datetime]      NOT NULL,
    [Email] [nvarchar](60),
    [EmailConfirmed] [bit] Default ((0)) NOT NULL,
    [PasswordHash] [nvarchar] (100),
    [SecurityStamp] [nvarchar] (60),
    [PhoneNumber] [nvarchar] (15),
    [PhoneNumberConfirmed] [bit] Default ((0)) NOT NULL,
    [TwoFactorEnabled] [bit] Default ((0)) NOT NULL,
    [LockoutEndDateUtc] [datetime],
    [LockoutEnabled] [bit] Default ((0)) NOT NULL,
    [AccessFailedCount] [int] Default ((0)) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY ([Id])
);
GO

INSERT INTO AspNetUsers(Id, UserName, AcId, LocId, PasswordHash, SecurityStamp, CreateDate )
SELECT UserProfile.UserId, UserProfile.UserName, UserProfile.BaId, UserProfile.OfcId,
webpages_Membership.Password, webpages_Membership.PasswordSalt, webpages_Membership.CreateDate
FROM UserProfile
LEFT OUTER JOIN webpages_Membership ON UserProfile.UserId = webpages_Membership.UserId
GO

If I change the AspNetUsers CreateDate column to NULL it successfully copies the datetimes from table to table so I know all the column names and types are correct. (After doing this I ran ALTER TABLE [dbo].[AspNetUsers] ALTER COLUMN [CreateDate] datetime NOT NULL and got the same error)

This is happening with the Production copy of the database. I have a development copy of the database generated through the same EF code first code and it successfully copies the data into the CreateDate NOT NULL field.

I'm at wits end at this point. Why am I getting a Cannot insert the value NULL into column error when the source data is valid datetimes? Or why doesn't it recognize the CreateDate columns data as valid datetimes?

Answer 1

You have some users in the UserProfile that haven't corresponding users in the webpages_Membership, so you try to insert users without any information. You must use INNER JOIN instead of LEFT OUTER JOIN or provide the default values for users which haven't corresponding information.