謝謝回復。但因為這個是要做排程自動上傳的，所以手動修改檔案內容的方式無法符合要求。
目前有嘗試著先使用python針對檔案內容做修改，但目前還是遇到一些問題。

希望可以的話能夠單純靠DB本身的語法就能解決問題。

-- 模擬 CSV bulk 暫存Table
CREATE TABLE TableX
(ID varchar(200));

INSERT INTO TableX 
VALUES
('aaa1\,111,bbb,ccc,ddd\,221'+CHAR(10)+
'aaa2\,111,bbb,ccc,ddd\,222'+CHAR(10)+
'aaa3\,111,bbb,ccc,ddd\,223'+CHAR(10)+
'aaa4\,111,bbb,ccc,ddd\,224'+CHAR(10)+
'aaa5\,111,bbb,ccc,ddd\,225'+CHAR(10)),
('kkk1\,111,bbb,,ddd\,221'+CHAR(10)+
'kkk2\,111,,ccc,ddd\,222'+CHAR(10)),
('yy,hh,,'+CHAR(10)),
('zz,,,'+CHAR(10));

-- 目的 Table
CREATE TABLE TableY
(YF_1 varchar(20),
YF_2 varchar(20),
YF_3 varchar(20),
YF_4 varchar(20));

Declare @DynFieldCountStr Nvarchar(Max) = ''
Declare @DynFieldCount Int = 0
Declare @DynFieldData Nvarchar(Max) = ''
Declare @FinalFieldData Nvarchar(Max) = ''
Declare @FinalFieldDataTempA Nvarchar(Max) = ''
Declare @FieldCountSql Nvarchar(Max) = ''
Declare @SqlFrom Nvarchar(Max) = ''
Declare @K Int = 1
Declare @UnionSql Nvarchar(Max) = ''
Declare @FixFieldNameSql Nvarchar(Max) = ''
Declare @TempSql Nvarchar(Max) = ''
Declare @TempSql2 Nvarchar(Max) = ''

Set @TempSql2 = '(
SELECT Replace(Replace(ID,''\,'',''#''),CHAR(10),'','')
AS BODY1
FROM TableX
) AS R '

-- 計算 BODY1 內要分拆的欄位最大值
Set @FieldCountSql = 'Select @DynFieldCountStr = Max(Len(BODY1)-Len(Replace(BODY1,'','',''''))) From ' + @TempSql2
Exec Sp_executesql @FieldCountSql, N'@DynFieldCountStr Nvarchar(Max) Out', @DynFieldCountStr Out

Set @DynFieldCount = Cast(Isnull(@DynFieldCountStr,'0') As Int)
If @DynFieldCount > 0
Begin
  Set @DynFieldCount = @DynFieldCount + 1
  -- Print @DynFieldCount

  Set @FixFieldNameSql = 'Select ' 
  -- BODY1 用 Xml 方式分割
  Set @SqlFrom = ' From (' + @FixFieldNameSql +
  'Cast(''<Tr><Td>'' + Replace(BODY1, '','', ''</Td><Td>'') + ''</Td></Tr>'' As Xml) Vals ' + 
  'From ' + @TempSql2 +
  'Where Len(RTrim(BODY1)) > 0 ) As D '

  While @K<=@DynFieldCount
  Begin
    -- 產生新的欄位名稱
    If (@K > 1)
    Begin
      Set @UnionSql = 'Union All '
    End
    Select @DynFieldData =
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FD_1],' +
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 1 As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FD_2],' +
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 2 As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FD_3],' +
    'D.Vals.value(''(/Tr/Td)[' + Cast(@K + 3 As Nvarchar(4)) + ']'',''Nvarchar(Max)'')' + ' As [FD_4],' 
    -- 
    Set @DynFieldData = Left(@DynFieldData, Len(@DynFieldData) - 1)
    Set @FinalFieldData = @FinalFieldData + @UnionSql + @FixFieldNameSql + @DynFieldData + @SqlFrom
    Set @K = @K + 4
  End

  -- Print @FinalFieldData
  Set @TempSql = 'From (' + @FinalFieldData + ') As M ' +
  'Where Len(RTrim([FD_1])) + Len(RTrim([FD_2])) + Len(RTrim([FD_3])) + Len(RTrim([FD_4])) > 0 '
   --
  Set @FinalFieldDataTempA = 'Insert Into TableY (YF_1,YF_2,YF_3,YF_4) ' +
  'Select ' + 
  'Replace(FD_1,''#'',''\,''),' + 
  'Replace(FD_2,''#'',''\,''),' +
  'Replace(FD_3,''#'',''\,''),' +
  'Replace(FD_4,''#'',''\,'') ' +
  @TempSql
  Exec (@FinalFieldDataTempA)  
  --
  Select *
  From TableY
  Order By YF_1
End

Demo