Conversion failed when converting the NVARCHAR to BIT

Question

Trying to get some clarification as to why this error is occurring. I think it may have something to do with my input data having NULL values but every example I've searched for doesn't really suit my needs.

This is the error I am receiving:

Conversion failed when converting the nvarchar value 'Y' to data type bit.

This is my code:

 CREATE TABLE table (
    ID INT IDENTITY(1, 1) PRIMARY KEY
        .
        .
        .
    ,CFD BIT 
)
;
INSERT INTO table ([CFD]
                  .
                  .
                  .
                  )
SELECT
        stg.[Flag] AS [CFD]
        .
        .
        .
FROM staging stg
;

The column [Flag] being pulled from the staging table contains values 'Y' or NULL. Basically, I am trying to rename the [Flag] column as [CFD] and change values from 'Y' to 1 and NULL to 0.

I have attempted to implement a CASE WHEN statement with little success.

Answer 1

You mention that you have tried the case statement but as you have not provided it I am unable to check if it is correct. I would do it something like this:

INSERT INTO table ([CFD])
SELECT
    CASE
        WHEN stg.[Flag] = 'Y' THEN 1
        ELSE 0
    END AS [CFD]
FROM staging stg;