unable to create view because of different number of columns from different tables

Create View [lossrun].vw_StagingDailyClaim as 
SELECT 
TPA,
ClaimNumber,
ClientName,
ClaimStatus,
DateReported,
DateOfLoss,
EntryDate,
DateClosed,
DateReopened,
AccidentDescription,
EffectiveDate,
ExpirationDate,
PolicyNumber,
AccidentState,
RiskState,
JurisdictionState,
Carrier,
LOBDescription,
ProgramCode,
PEO,
LossPaid,
MedicalPaid,
SubroRecovery,
SalvageRecovery,
DeductRecovery,
ExpensePaid,
Recovery,
LossOS,
MedicalOS,
ExpenseOS,
Incurred,
InsuredName,
DateCreated,
CreatedBy,
UpdatedBy,
DateUpdated,
CatastropheNumb,
IndemnityRecovery,
ExpenseRecovery,
CurrentAdjuster
FROM
(
SELECT       'Athens' as  TPA , ClaimNumber, ClaimStatus, DateReported, DateOfLoss, EntryDate, DateClosed, DateReopened, AccidentDescription, EffectiveDate, ExpirationDate, PolicyNumber, AccidentState, RiskState, JurisdictionState, Carrier, 
                         LOBDescription, ProgramCode, PEO, LossPaid, ExpensePaid, Recovery, LossOS, ExpenseOS, Incurred, InsuredName, DateCreated, CreatedBy, UpdatedBy, DateUpdated
FROM            Lossrun.LandingAthensDailyClaim
UNION 
SELECT        'CBSC' as  TPA
,ClaimNumber
, ClaimStatus
, DateReported
, DateOfLoss
, EntryDate
, DateClosed
, DateReopened
, AccidentDescription
, EffectiveDate
, ExpirationDate
, PolicyNumber
, AccidentState
, RiskState
, JurisdictionState
, Carrier
, LOBDescription, ProgramCode, PEO, LossPaid, ExpensePaid, Recovery, LossOS, ExpenseOS, Incurred, InsuredName, DateCreated, CreatedBy, UpdatedBy, DateUpdated
FROM            Lossrun.LandingCBSCDailyClaim
UNION ALL
SELECT   
'NARS' as TPA
,ClaimNumber
,ClientName
,ClaimStatus
,cast(DateReported as Date) DateReported
,cast(DateOfLoss as Date) DateOfLoss
,EntryDate
,cast(DateClosed as Date) DateClosed
,cast(DateReopened as Date) DateReopened
,AccidentDescription
,EffectiveDate
,ExpirationDate
,PolicyNumber
,AccidentState
,RiskState
,JurisdictionState
,Carrier
,LOBDescription
,ProgramCode
,PEO
,LossPaid
,MedicalPaid
,SubroRecovery
,SalvageRecovery
,DeductRecovery
,ExpensePaid
,Recovery
,LossOS
,MedicalOS
,ExpenseOS
,Incurred
,InsuredName
,DateCreated
,CreatedBy
,UpdatedBy
,DateUpdated
,CatastropheNumb
 from Lossrun.LandingNARSDailyClaim
 UNION
SELECT
'RMA' as TPA
,ClaimNumber
,ClientName
,ClaimStatus
,cast(DateReported as Date) DateReported
,cast(DateOfLoss as Date) DateOfLoss
,EntryDate
,cast(DateClosed as Date) DateClosed
,cast(DateReopened as Date) DateReopened
,AccidentDescription
,EffectiveDate
,ExpirationDate
,PolicyNumber
,AccidentState
,RiskState
,JurisdictionState
,Carrier
,LOBDescription
,ProgramCode
,PEO
,LossPaid
,MedicalPaid
,SubroRecovery
,SalvageRecovery
,DeductRecovery
,ExpensePaid
,Recovery
,LossOS
,MedicalOS
,ExpenseOS
,Incurred
,InsuredName
,DateCreated
,CreatedBy
,UpdatedBy
,DateUpdated
,CatastropheNumb
 from Lossrun.LandingNARSDailyClaim
 )Q 

This is error statement in SSMS “All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.” why can’t i create view like this?

Answer

All the sub queries need to have the same number of columns, with the same names and datatypes, and in the same order. If some subqueries/tables don’t have columns that other subqueries/tables do, you still need to define those columns.

For example, Lossrun.LandingAthensDailyClaim doesn’t have a ClientName column defined in its subquery. But based on the view definition and other subqueries, we still need to add it. We can use NULL for the value in this case.

Taking your view definition, I came up with the following query:

CREATE VIEW lossrun.vw_StagingDailyClaim
AS
SELECT
    TPA
    ,ClaimNumber
    ,ClientName
    ,ClaimStatus
    ,DateReported
    ,DateOfLoss
    ,EntryDate
    ,DateClosed
    ,DateReopened
    ,AccidentDescription
    ,EffectiveDate
    ,ExpirationDate
    ,PolicyNumber
    ,AccidentState
    ,RiskState
    ,JurisdictionState
    ,Carrier
    ,LOBDescription
    ,ProgramCode
    ,PEO
    ,LossPaid
    ,MedicalPaid
    ,SubroRecovery
    ,SalvageRecovery
    ,DeductRecovery
    ,ExpensePaid
    ,Recovery
    ,LossOS
    ,MedicalOS
    ,ExpenseOS
    ,Incurred
    ,InsuredName
    ,DateCreated
    ,CreatedBy
    ,UpdatedBy
    ,DateUpdated
    ,CatastropheNumb
    ,IndemnityRecovery
    ,ExpenseRecovery
    ,CurrentAdjuster
FROM
(
    SELECT
        TPA = 'Athens'
        ,ClaimNumber
        ,ClientName = NULL
        ,ClaimStatus
        ,DateReported
        ,DateOfLoss
        ,EntryDate
        ,DateClosed
        ,DateReopened
        ,AccidentDescription
        ,EffectiveDate
        ,ExpirationDate
        ,PolicyNumber
        ,AccidentState
        ,RiskState
        ,JurisdictionState
        ,Carrier
        ,LOBDescription
        ,ProgramCode
        ,PEO
        ,LossPaid
        ,MedicalPaid = NULL
        ,SubroRecovery = NULL
        ,SalvageRecovery = NULL
        ,DeductRecovery = NULL
        ,ExpensePaid
        ,Recovery
        ,LossOS
        ,MedicalOS = NULL
        ,ExpenseOS
        ,Incurred
        ,InsuredName
        ,DateCreated
        ,CreatedBy
        ,UpdatedBy
        ,DateUpdated
        ,CatastropheNumb = NULL
        ,IndemnityRecovery = NULL
        ,ExpenseRecovery = NULL
        ,CurrentAdjuster = NULL
    FROM Lossrun.LandingAthensDailyClaim
    UNION
    SELECT
        TPA = 'CBSC'
        ,ClaimNumber
        ,ClientName = NULL
        ,ClaimStatus
        ,DateReported
        ,DateOfLoss
        ,EntryDate
        ,DateClosed
        ,DateReopened
        ,AccidentDescription
        ,EffectiveDate
        ,ExpirationDate
        ,PolicyNumber
        ,AccidentState
        ,RiskState
        ,JurisdictionState
        ,Carrier
        ,LOBDescription
        ,ProgramCode
        ,PEO
        ,LossPaid
        ,MedicalPaid = NULL
        ,SubroRecovery = NULL
        ,SalvageRecovery = NULL
        ,DeductRecovery = NULL
        ,ExpensePaid
        ,Recovery
        ,LossOS
        ,MedicalOS = NULL
        ,ExpenseOS
        ,Incurred
        ,InsuredName
        ,DateCreated
        ,CreatedBy
        ,UpdatedBy
        ,DateUpdated
        ,CatastropheNumb = NULL
        ,IndemnityRecovery = NULL
        ,ExpenseRecovery = NULL
        ,CurrentAdjuster = NULL
    FROM Lossrun.LandingCBSCDailyClaim
    UNION ALL
    SELECT
        TPA = 'NARS'
        ,ClaimNumber
        ,ClientName
        ,ClaimStatus
        ,DateReported = CAST(DateReported AS DATE)
        ,DateOfLoss = CAST(DateOfLoss AS DATE)
        ,EntryDate
        ,DateClosed = CAST(DateClosed AS DATE)
        ,DateReopened = CAST(DateReopened AS DATE)
        ,AccidentDescription
        ,EffectiveDate
        ,ExpirationDate
        ,PolicyNumber
        ,AccidentState
        ,RiskState
        ,JurisdictionState
        ,Carrier
        ,LOBDescription
        ,ProgramCode
        ,PEO
        ,LossPaid
        ,MedicalPaid
        ,SubroRecovery
        ,SalvageRecovery
        ,DeductRecovery
        ,ExpensePaid
        ,Recovery
        ,LossOS
        ,MedicalOS
        ,ExpenseOS
        ,Incurred
        ,InsuredName
        ,DateCreated
        ,CreatedBy
        ,UpdatedBy
        ,DateUpdated
        ,CatastropheNumb
        ,IndemnityRecovery = NULL
        ,ExpenseRecovery = NULL
        ,CurrentAdjuster = NULL
    FROM Lossrun.LandingNARSDailyClaim
    UNION
    SELECT
        TPA = 'RMA'
        ,ClaimNumber
        ,ClientName
        ,ClaimStatus
        ,DateReported = CAST(DateReported AS DATE)
        ,DateOfLoss = CAST(DateOfLoss AS DATE)
        ,EntryDate
        ,DateClosed = CAST(DateClosed AS DATE)
        ,DateReopened = CAST(DateReopened AS DATE)
        ,AccidentDescription
        ,EffectiveDate
        ,ExpirationDate
        ,PolicyNumber
        ,AccidentState
        ,RiskState
        ,JurisdictionState
        ,Carrier
        ,LOBDescription
        ,ProgramCode
        ,PEO
        ,LossPaid
        ,MedicalPaid
        ,SubroRecovery
        ,SalvageRecovery
        ,DeductRecovery
        ,ExpensePaid
        ,Recovery
        ,LossOS
        ,MedicalOS
        ,ExpenseOS
        ,Incurred
        ,InsuredName
        ,DateCreated
        ,CreatedBy
        ,UpdatedBy
        ,DateUpdated
        ,CatastropheNumb
        ,IndemnityRecovery = NULL
        ,ExpenseRecovery = NULL
        ,CurrentAdjuster = NULL
    FROM Lossrun.LandingNARSDailyClaim
) Q;