Showing posts with label How to insert different comma separated values into different columns in SQL Server?. Show all posts
Showing posts with label How to insert different comma separated values into different columns in SQL Server?. Show all posts


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?


Followers

Popular Posts