Handle with Delimeter ^ (Continuation from Previous Question)

Question

Hi All,

I have SSIS to load data from txt file into SQL server. First, I will load the file into SQL and from SQL table I need to extract transfer to another SQL table with the desired output. In previous question, I get the solution from @Yitzhak Khabinsky as per attached below but I missed out to add column LOAD_DATETIME and FILENAME. How can I fine tune the sql solution below to get the load datetime and filename as per shown in desired output below? I'm very new to SQL and not able to modified the SQL code for load datetime and filename :'( . Kindly Please help.

DDL and Sample Data Population

  -- DDL and sample data population, start  
  DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(50), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(50), LOAD_DATETIME DATETIME, FILENAME VARCHAR(100))  
  INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP,LOAD_DATETIME,FILENAME ) VALUES  
  ('2287250^2287307^2287308','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91','20220817163329^20220817165355^20220817165358','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt'),  
  ('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt');  
  -- DDL and sample data population, end  

SQL Code from Previous Answer without column LOAD_DATETIME and FILENAME

DECLARE @separator CHAR(1) = '^';  
      
 ;WITH rs AS  
 (  
 SELECT *  
 FROM @tbl AS t  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(AUTONUM, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t1(a)  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(UKEY, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t2(b)  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(INSCODE, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t3(c)  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(ACTION_TIMESTAMP, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t4(d)  
 ), rs_AUTONUM AS  
 (  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS AUTONUM  
 FROM rs CROSS APPLY a.nodes('/root/r') AS t(c)  
 )  
 , rs_UKEY AS  
 (  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS UKEY  
 FROM rs CROSS APPLY b.nodes('/root/r') AS t(c)  
 ), rs_INSCODE AS  
 (  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS INSCODE  
 FROM rs CROSS APPLY c.nodes('/root/r') AS t(c)  
 ), rs_ACTION_TIMESTAMP AS  
 (  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS ACTION_TIMESTAMP  
 FROM rs CROSS APPLY d.nodes('/root/r') AS t(c)  
 )  
 SELECT x1.*, UKEY, INSCODE, ACTION_TIMESTAMP  
 FROM rs_AUTONUM AS x1 INNER JOIN rs_UKEY AS x2 ON x1.ID = x2.ID  
 INNER JOIN rs_INSCODE AS x3 ON x2.ID = x3.ID  
 INNER JOIN rs_ACTION_TIMESTAMP AS x4 ON x3.ID = x4.ID;  

Answer 1

Hi @jn93 ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(50), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(50), LOAD_DATETIME DATETIME, FILENAME VARCHAR(100));  
INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP, LOAD_DATETIME, FILENAME) VALUES  
('2287250^2287307^2287308','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91','20220817163329^20220817165355^20220817165358','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt'),  
('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322','2022-10-21 09:51:22.957','RL_TB_REFER_ACTION_20220818.txt');  
-- DDL and sample data population, end  
  
DECLARE @LOAD_DATETIME DATETIME  
 , @FILENAME VARCHAR(100);  
  
SELECT TOP(1) @LOAD_DATETIME = LOAD_DATETIME, @FILENAME = FILENAME  
FROM @tbl;  
  
DECLARE @separator CHAR(1) = '^';  
  
;WITH rs AS  
(  
 SELECT *  
 FROM @tbl AS t  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(AUTONUM, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t1(a)  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(UKEY, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t2(b)  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(INSCODE, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t3(c)  
 CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
   REPLACE(ACTION_TIMESTAMP, @separator, ']]></r><r><![CDATA[') +   
   ']]></r></root>' AS XML)) AS t4(d)  
), rs_AUTONUM AS  
(  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS AUTONUM  
 FROM rs CROSS APPLY a.nodes('/root/r') AS t(c)  
)  
, rs_UKEY AS  
(  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS UKEY  
 FROM rs CROSS APPLY b.nodes('/root/r') AS t(c)  
), rs_INSCODE AS  
(  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS INSCODE  
 FROM rs CROSS APPLY c.nodes('/root/r') AS t(c)  
), rs_ACTION_TIMESTAMP AS  
(  
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
 , t.c.value('(./text())[1]', 'VARCHAR(50)') AS ACTION_TIMESTAMP  
 FROM rs CROSS APPLY d.nodes('/root/r') AS t(c)  
)  
SELECT x1.*, x2.UKEY, x3.INSCODE, x4.ACTION_TIMESTAMP  
 , @LOAD_DATETIME AS LOAD_DATETIME, @FILENAME AS FILENAME  
FROM rs_AUTONUM AS x1 INNER JOIN rs_UKEY AS x2 ON x1.ID = x2.ID  
 INNER JOIN rs_INSCODE AS x3 ON x2.ID = x3.ID  
 INNER JOIN rs_ACTION_TIMESTAMP AS x4 ON x3.ID = x4.ID  
ORDER BY x1.ID;