How to insert different comma separated values into different columns in SQL Server?

/
0 Comments


How to insert different comma separated values into different columns in SQL Server?

Step 1: Create a table in which we will insert the CSV Data using the below query:

CREATE TABLE Test_Table(col1 NVARCHAR(200), col2 NVARCHAR(200), col3 NVARCHAR(200),col4 NVARCHAR(200),col5 NVARCHAR(200))

Step 2: Create an SP that will break the CSV string into columns and insert the data into the above created table as shown below:

ALTER PROC Test_SP
(
    @csvText NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @status NVARCHAR(20) = ''
DECLARE @error_desc NVARCHAR(MAX)
DECLARE @csv_data TABLE
  (
    id INT NOT NULL IDENTITY(1, 1),
    csv_text NVARCHAR(MAX)
  )

INSERT INTO @csv_data(csv_text)
SELECT SUBSTRING([VALUE],1,LEN([VALUE])-1) FROM STRING_SPLIT(@csvText, CHAR(10)) WHERE LEN(VALUE) > 0


;WITH cte_csv_data AS (
    SELECT DISTINCT id
    , S.a.value('(/H/r)[1]', 'NVARCHAR(200)') AS col1
    , S.a.value('(/H/r)[2]', 'NVARCHAR(200)') AS col2
    , S.a.value('(/H/r)[3]', 'NVARCHAR(200)') AS col3
    , S.a.value('(/H/r)[4]', 'NVARCHAR(200)') AS col4
    , S.a.value('(/H/r)[5]', 'NVARCHAR(200)') AS col5
FROM
  (
    SELECT *,CAST (N'' + REPLACE(csv_text, ',', '') + '' AS XML) AS [vals]
    FROM @csv_data WHERE id > 0) d
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
  )
INSERT INTO Test_Table
(
    col1,
    col2,
    col3,
    col4,
    col5
)
Select
    col1,
    col2,
    col3,
    col4,
    col5
FROM
    cte_csv_data
END
GO

Step 3: Let's execute the above created SP with CSV String as the parameter:

exec Test_SP 'surya, construction, 23, 2, 234443 
sandeep, IT, 20, 1, 455443'

Step 4: The output is as shown below:

How to insert different comma separated values into different columns in SQL Server?




About Post Author

Microsoft Certified SQL Server Developer

You may also like

No comments:

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.

Followers

Popular Posts