CODEWITHSUNDEEP
sqlsql-server
kcd
kcd

Reputation: 37

How can I replace the NULL with a value when using an INSERT?

I am trying to find out why this section of the query wouldn't work

ISNULL(nightlyTable.AppointmentID, 
baseTable.AppointmentID) AS AppointmentID

Is there another way of writing this section? I need for it to show an AppointmentID when the value is NULL.

Thank you!!

USE [AdventureWorksLT]
GO

SET IDENTITY_INSERT [dbo].[tempTable] ON

INSERT INTO [dbo].[tempTable]
           ([AppointmentID]
           ,[CustName]
           ,[CustAddress]
           ,[CustPhone])

(SELECT 

    ISNULL(nightlyTable.AppointmentID, 
    baseTable.AppointmentID) AS AppointmentID

    , nightlyTable.CustName
    , nightlyTable.CustAddress
    , nightlyTable.CustPhone 
FROM nightlyTable
FULL OUTER JOIN  baseTable ON (baseTable.AppointmentID = 
nightlyTable.AppointmentID)
WHERE EXISTS (SELECT nightlyTable.* EXCEPT SELECT baseTable.*))
GO

SET IDENTITY_INSERT [dbo].[tempTable] OFF;

Sample result. I'd like for row 10 to show AppointmentID '10' instead of NULL.

AppointmentID   CustName    CustAddress             CustPhone
 1              Salvad0r    1 Main Street North     76197081653
 NULL           NULL        NULL                    NULL
 3              Gilbert     51 Main Street South    23416310745
 NULL           NULL        NULL                    NULL
 5              Jorge       176 Washington Street   7078675309
 NULL           NULL        NULL                    NULL
 7              Stella      192 Church Street       78584836879
 NULL           NULL        NULL                    NULL
 9              Heyy        214 High Street         57288772686
 NULL           NULL        NULL                    NULL
 11             Newbie      21 Jump Street          76086753019

Sample tables:

enter image description here

Upvotes: 0

Views: 67

Answers (2)

Hogan
Hogan

Reputation: 70523

So you want to select all the elements in base table that are not in night table. To do that you left join base table to night table and then look where it was unable to join. Like this:

select b.*
from baseTable b
left join nightlyTable n on n.AppointmentID = b.AppointmentID
where n.AppointmentID is null

If you also want to include existing data in nightly table the easy way is with a union, like this:

select *
from nightlyTable

UNION ALL

select b.*
from baseTable b
left join nightlyTable n on n.AppointmentID = b.AppointmentID
where n.AppointmentID is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This code:

ISNULL(nightlyTable.AppointmentID, baseTable.AppointmentID) AS AppointmentID

does not work because you have no table alias for basetable defined in the FROM clause.

Upvotes: 1

Related Questions