CODEWITHSUNDEEP
sqlsql-serverinsertnullcase
Aaron Troeger
Aaron Troeger

Reputation: 165

Insert Statement Check Is NULL SQL Server 2008

My small SQL query is just importing data from an old database to a new one. My new database does not allow ProjectNo, ProjectName or LeaderID to be a NULL value, so my code needs to check if the value is NULL and if it is, add in the default values for ProjectName and LeaderID (ProjectNo is the primary Key of the old table, so cannot be NULL).

As far as I can tell, I have written the case statement correctly but I keep getting the following error:

Cannot insert the value NULL into column 'ProjectName', table 'ERP.dbo.Project'; column does not allow nulls. INSERT fails.

I am using SQL Server 2008 as well

INSERT INTO [ERP].[dbo].[Project] ([ProjectID], [ProjectName], [LeaderID])
   SELECT 
       ProjectNo, 
       CASE 
          WHEN ProjectName IS NULL THEN 'Unknown' 
       END, 
       CASE 
          WHEN ProjectLeaderID IS NULL THEN 1 
       END 
   FROM 
       Multitech.dbo.Projects
GO

Upvotes: 0

Views: 10877

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

Using ISNULL

 INSERT INTO [ERP].[dbo].[Project]
       ([ProjectID]
       ,[ProjectName]
       ,[LeaderID])
 SELECT ProjectNo,
       ISNULL(ProjectName, 'Unknown'),
       ISNULL(ProjectLeaderID, 1)
 FROM Multitech.dbo.Projects

Add missing ELSE and your code will work too. CASE documentation

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression.

 INSERT INTO [ERP].[dbo].[Project]
       ([ProjectID]
       ,[ProjectName]
       ,[LeaderID])
 SELECT ProjectNo,
        CASE WHEN ProjectName IS NULL THEN 'Unknown' ELSE ProjectName END,
        CASE WHEN ProjectLeaderID IS NULL THEN 1 ELSE ProjectLeaderID END
 FROM Multitech.dbo.Projects

Upvotes: 5

Related Questions