title: How to convert YES/NO to BIT automatically in SQL Server?
body: <p>I want to override system defined conversion in SQL Server. is that possible.</p>
  
  <p><strong>Problem Statement</strong></p>
  
  <p>I have a large csv file to upload data in database. There is a column with <code>BIT</code> type contains <code>True/False</code>, <code>Yes/No</code>, <code>1/0</code></p>
  
  <p>When I used bulk insertion <code>True/False</code> and '1/0' will be consider as right value but <code>Yes/No</code> (as expected) will throw an conversion error.</p>
  
  <p>Is there any way to convert <code>Yes/No</code> without any looping or one by one value?</p>
  
  <p><strong>Edit</strong></p>
  
  <p>Custom data type <code>PanelMemberType</code></p>
  
  <pre><code>CREATE TYPE [dbo].[PanelMemberType] AS TABLE(
   [Email] [nvarchar](255) NOT NULL,
   [LocationName] [nvarchar](255) NOT NULL,
   [OptInPermission] [nvarchar](255) NOT NULL
  )
  GO
  </code></pre>
  
  <p>Stored procedure code:</p>
  
  <pre><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
  </code></pre>