SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- 模擬 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));

SELECT *
FROM TableX;


ID


aaa1,111,bbb,ccc,ddd,221
aaa2,111,bbb,ccc,ddd,222
aaa3,111,bbb,ccc,ddd,223
aaa4,111,bbb,ccc,ddd,224
aaa5,111,bbb,ccc,ddd,225


kkk1,111,bbb,,ddd,221
kkk2,111,,ccc,ddd,222


yy,hh,,


zz,,,



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

SELECT *
FROM TableY;


YF_1
YF_2
YF_3
YF_4


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



YF_1
YF_2
YF_3
YF_4


aaa1,111
bbb
ccc
ddd,221

aaa2,111
bbb
ccc
ddd,222

aaa3,111
bbb
ccc
ddd,223

aaa4,111
bbb
ccc
ddd,224

aaa5,111
bbb
ccc
ddd,225

kkk1,111
bbb

ddd,221

kkk2,111

ccc
ddd,222

yy
hh



zz
YF_1	YF_2	YF_3	YF_4
aaa1,111	bbb	ccc	ddd,221
aaa2,111	bbb	ccc	ddd,222
aaa3,111	bbb	ccc	ddd,223
aaa4,111	bbb	ccc	ddd,224
aaa5,111	bbb	ccc	ddd,225
kkk1,111	bbb		ddd,221
kkk2,111		ccc	ddd,222
yy	hh
zz