How to convert YES/NO to BIT automatically in SQL Server?

Question

I want to override system defined conversion in SQL Server. is that possible.

Problem Statement

I have a large csv file to upload data in database. There is a column with BIT type contains True/False, Yes/No, 1/0

When I used bulk insertion True/False and '1/0' will be consider as right value but Yes/No (as expected) will throw an conversion error.

Is there any way to convert Yes/No without any looping or one by one value?

Edit

Custom data type PanelMemberType

CREATE TYPE [dbo].[PanelMemberType] AS TABLE(
 [Email] [nvarchar](255) NOT NULL,
 [LocationName] [nvarchar](255) NOT NULL,
 [OptInPermission] [nvarchar](255) NOT NULL
)
GO

Stored procedure code:

ALTER PROCEDURE [dbo].[PanelMemberBulkUpdate]
     @tblPanelMember PanelMemberType READONLY, 
     @PanelID int, 
     @UserID nvarchar(128)
AS
BEGIN
     SET NOCOUNT ON;

     MERGE INTO PanelMember p1
     USING @tblPanelMember p2 ON p1.Email= p2.Email

     WHEN MATCHED THEN
         UPDATE SET  
             p1.PanelID = @PanelID,
             p1.LocationID = (SELECT TOP(1) [LocationID] 
                              FROM [dbo].[Location] 
                              WHERE [LocationName] = p2.LocationName),
             p1.Email = p2.Email,
             p1.OptInPermission = CONVERT(BIT, p2.OptInPermission),
             p1.DateAdded = p1.DateAdded, 
             p1.DateLastUpdated = (SELECT GETDATE()),
             p1.LastUpdateUserID = @UserID

     WHEN NOT MATCHED THEN
          INSERT (PanelID, LocationID, Email, OptInPermission, DateAdded) 
          VALUES (@PanelID, (SELECT TOP(1) [LocationID] 
                             FROM [dbo].[Location] 
                             WHERE [LocationName] = p2.LocationName), 
                  p2.Email, CONVERT(BIT, p2.OptInPermission),
                  (SELECT GETDATE()));
END

Answer 1

Just apply a bit more logic rather than trying to blindly convert to bit:

p1.OptInPermission = convert( bit,
      CASE p2.OptInPermission
        WHEN 'Yes' THEN 1
        WHEN 'No' THEN 0
        ELSE p2.OptInPermission END)

(Or, to avoid having to duplicate this logic in both branches of the MERGE, do it in the source:

USING (select Email,LocationName,convert( bit,
          CASE p2.OptInPermission
            WHEN 'Yes' THEN 1
            WHEN 'No' THEN 0
            ELSE p2.OptInPermission END as OptInPermission from @tblPanelMember) p2

)

Answer 2

Inserts records from CSV file into staging table first.

Then run the actual insert into your table and your select statement should look like this:

INSERT INTO ActualTable(column1, column2, column3)
SELECT column1
    , column2
    , CAST(CASE column3
        WHEN 'Yes' THEN 1
        WHEN 'No'  THEN 0
        ELSE column3
    END AS BIT) AS YourBitColumn
FROM StagingTable;

Based on your approach, this should be working:

ALTER PROCEDURE [dbo].[PanelMemberBulkUpdate]
(
    @tblPanelMember PanelMemberType READONLY
    , @PanelID INT
    , @UserID NVARCHAR(128)
)
AS
BEGIN TRY
    SET NOCOUNT ON;

    MERGE INTO PanelMember p1
    USING @tblPanelMember p2
        ON p1.Email = p2.Email
    WHEN MATCHED THEN
    UPDATE
    SET p1.PanelID = @PanelID
        , p1.LocationID = (SELECT TOP (1) [LocationID] FROM [dbo].[Location] WHERE [LocationName] = p2.LocationName)
        , p1.Email = p2.Email
        , p1.OptInPermission = CONVERT(BIT, CASE p2.OptInPermission
            WHEN 'Yes' THEN 1
            WHEN 'No' THEN 0
            ELSE p2.OptInPermission
        END)
        , p1.DateAdded = p1.DateAdded
        , p1.DateLastUpdated = GETDATE()
        , p1.LastUpdateUserID = @UserID
    WHEN NOT MATCHED THEN
    INSERT (PanelID, LocationID, Email, OptInPermission, DateAdded)
    VALUES
    (
        @PanelID
        , (SELECT TOP (1) [LocationID] FROM [dbo].[Location] WHERE [LocationName] = p2.LocationName)
        , p2.Email
        , CONVERT(BIT, CASE p2.OptInPermission
            WHEN 'Yes' THEN 1
            WHEN 'No' THEN 0
            ELSE p2.OptInPermission
        END)
        , GETDATE()
    );
END TRY
BEGIN CATCH
    THROW;
END CATCH

You cannot override default function, but you can write your own one. for instance:

CREATE FUNCTION dbo.ConvertBit
(
    @Input VARCHAR(5)
)
RETURNS TABLE AS RETURN
SELECT CONVERT(BIT, CASE @Input
        WHEN 'True'     THEN 1
        WHEN 'Yes'      THEN 1
        WHEN '1'        THEN 1
        WHEN 'False'    THEN 0
        WHEN 'No'       THEN 0
        WHEN '0'        THEN 0
    END AS BitColumn;

And then just query it this way:

ALTER PROCEDURE [dbo].[PanelMemberBulkUpdate]
(
    @tblPanelMember PanelMemberType READONLY
    , @PanelID INT
    , @UserID NVARCHAR(128)
)
AS
BEGIN TRY
    SET NOCOUNT ON;

    MERGE INTO PanelMember p1
    USING (
        SELECT T.Email
            , T.LocationName
            , B.OptInPermission
        FROM @tblPanelMember AS T
        CROSS APPLY dbo.ConvertBit(T.OptInPermission) AS B(OptInPermission)
        ) AS p2
        ON p1.Email = p2.Email
    WHEN MATCHED THEN
    UPDATE
    SET p1.PanelID = @PanelID
        , p1.LocationID = (SELECT TOP (1) [LocationID] FROM [dbo].[Location] WHERE [LocationName] = p2.LocationName)
        , p1.Email = p2.Email
        , p1.OptInPermission = p2.OptInPermission
        , p1.DateAdded = p1.DateAdded
        , p1.DateLastUpdated = GETDATE()
        , p1.LastUpdateUserID = @UserID
    WHEN NOT MATCHED THEN
    INSERT (PanelID, LocationID, Email, OptInPermission, DateAdded)
    VALUES
    (
        @PanelID
        , (SELECT TOP (1) [LocationID] FROM [dbo].[Location] WHERE [LocationName] = p2.LocationName)
        , p2.Email
        , p2.OptInPermission
        , GETDATE()
    );
END TRY
BEGIN CATCH
    THROW;
END CATCH