Insert failing on null but select shows no null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert failing on null but select shows no null

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2014-05-08 : 10:06:08

I am trying to run an INSERT SELECT FROM statement that fails with the error "Cannot insert the value NULL into column CarrierID" but when I run just the SELECT statement part of the INSERT no null values are returned so I am not sure why a null is being picked up. I do know the source table does have two rows where the Carrier_SCAC is '' but it is not null and the WHERE clause should be, and is, filtering out those two rows.

        INSERT INTO dbo.Driver (
            CarrierID,
            Name,
            Locked,
            DateLocked,
            Memo,
            UniqueIdentifier,
            CreatedByUserId,
            CreatedDate
        ) SELECT 
            (SELECT Id FROM RTS.dbo.Carrier WHERE Carrier.SCAC = RemoteTerminals.dbo.Driver.Carrier_SCAC),
            Driver_Name,
            COALESCE(Driver_Locked, 0),
            Driver_Locked_Date,
            Driver_Locked_Memo,
            NEWID(),
            0,
            GETDATE()
        FROM RemoteTerminals.dbo.Driver
        WHERE RemoteTerminals.dbo.Driver.Carrier_SCAC <> '';

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-08 : 10:31:18

and an extra condition , to be sure

(SELECT Id FROM RTS.dbo.Carrier WHERE Carrier.SCAC = RemoteTerminals.dbo.Driver.Carrier_SCAC AND ID IS NOT NULL )

sabinWeb MCP

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2014-05-08 : 10:52:13

I have tried adding the WHERE RemoteTerminals.dbo.Driver.Carrier_SCAC <> '' and just tried adding the AD IS ID NOT NUL to the sub-select and still get the error message.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2014-05-08 : 11:58:31

If I update the code to read like this everything works fine.

INSERT INTO dbo.Driver (
            CarrierID,
            Name,
            Locked,
            DateLocked,
            Memo,
            UniqueIdentifier,
            CreatedByUserId,
            CreatedDate
        ) SELECT
            c.id,
            d.Driver_Name,
            COALESCE(d.Driver_Locked, 0),
            d.Driver_Locked_Date,
            d.Driver_Locked_Memo,
            NEWID(),
            0,
            GETDATE()
        FROM Carrier c inner join RemoteTerminals.dbo.Driver d
		on c.SCAC = d.Carrier_SCAC
        and d.Carrier_SCAC != '';

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources