how not to update/insert a sql table with null/blank value for a certain field

Question

    			CREATE TABLE [dim].[patient](
				[dim_patient_current_de_key] [int] IDENTITY(1,1) NOT NULL,
				[id] [varchar](20) NULL,
				[client] [varchar](40) NULL,
				[DOB] [datetime] NULL,
				[Gender_Identity_code] [varchar](50) NULL,--type 2
				[dss_load_date] [datetime] NULL,
				[dss_start_date] [datetime] NULL,
				[dss_end_date] [datetime] NULL,
				[dss_current_flag] [char](1) NULL,
				[dss_version] [int] NULL,
				[dss_create_time] [datetime] NULL,
				[dss_update_time] [datetime] NULL,
				[StateID_number] [varchar](240) NULL,
				CONSTRAINT [dim_patient_demographi_idx_0] PRIMARY KEY CLUSTERED 
				-------- where the update (are for type 1 columns)
				UPDATE [dim].[dim_patient] WITH ( TABLOCK )
				SET     client =  changes.client_name
				, DOB =  changes.DOB
				, SSN =  changes.SSN
				,StateID_number = isnull(nullif(changes.StateID_number, ''), 
				[dim].[dim_patient].StateID_number)
						
				, dss_update_time = @v_current_datetime
				FROM 
				(
				SELECT stage_patient.id  id
				, stage_patient.client  client
				, stage_patient.DOB  DOB
				, stage_patient.Gender_Identity_code
				, stage_patient.StateID_number  StateID_number
				FROM [stage].[stage_patient] stage_patient
				EXCEPT
				SELECT dim_patient.id  id
				, dim_patient.client  client
				, dim_patient.DOB  DOB
			    , dim_patient.Gender_Identity_code
				, dim_patient.StateID_number  StateID_number
				FROM [dim].[dim_patient]
				WHERE dim_patient.dss_current_flag = 'Y'
				) AS changes
				WHERE dim_patient.id = changes.id
				AND   dim_patient.dss_current_flag = 'Y'
				----------------------------------------------------------------
				--============================================================================
				-- Insert new records
				--============================================================================
				INSERT INTO [dim].[patient] WITH ( TABLOCK )
				( id
				, client
				, DOB
				, Gender_Identity_code
				, StateID_number
				, dss_load_date
				, dss_start_date
				, dss_end_date
				, dss_current_flag
				, dss_version
				, dss_create_time
				, dss_update_time
				)
				SELECT DISTINCT 
				  stage_patient.id
				, stage_patient.client
				, stage_patient.DOB
				, stage_patient.Gender_Identity_code
				, stage_patient.StateID_number
				, stage_patient.dss_load_date
				, CASE WHEN vers.patid IS NULL
				THEN CAST('01-JAN-1900' AS datetime)
				ELSE @v_current_date
				END
				, CAST('31-DEC-2999' AS datetime)
				, 'Y'
				, CASE WHEN vers.patid IS NULL
				THEN 1
				ELSE vers.dss_version + 1
				END
				, @v_current_datetime
				, @v_current_datetime
				FROM [stage].[stage_patient] stage_patient
				LEFT OUTER JOIN (
				SELECT patid
				, MAX(dss_version) dss_version
				FROM [stage].[patient] patient
				GROUP BY id
				) AS vers
				ON    stage_patient.id = vers.id
				EXCEPT
				SELECT patient.id  id
				, patient.client  client
				, patient.DOB  DOB
				, patient.StateID_number  StateID_number
				, source.dss_load_date  dss_load_date
				, @v_current_date
				, CAST('31-DEC-2999' AS datetime)
				, 'Y'
				, dss_version + 1
				, @v_current_datetime
				, @v_current_datetime
				FROM [dim].[patient] patient
				JOIN
				(
				SELECT stage_patient.id AS id
				, stage_patient.dss_load_date AS dss_load_date
				FROM [stage].[stage_patient] stage_patient
				) AS source 
				ON    patient.id = source.id
				WHERE patient.dss_current_flag = 'Y'
Currently the code is inserting a second row with null for state_id which I dont want.

Answer 1

Hi @andrew omofonma

Currently the code is inserting a second row with null for state_id which I dont want.

You use ISNULL along with NULLIF function to convert NULL or blank value to something else, just like you used in your UPDATE script.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.