Save a string array into SQL database

Question

I have a C# function which saves data to a SQL Server database. I concatenate strings in one string like this: "name1,name2,name3,name4" and I want to send them to the database in one time, instead of looping through them and save them one by one.

So, in SQL server it looks like that

Declare @names varchar 
Set @names='name1,name2,name3,name4'

How can I split this array string and loop through it to insert each name into a different record?

I'm using SQL Server 2012.

Best way is to pass a table-valued parameter, but there are many other ways as well..this question has been answered many, many times on this site. Before you post a question, it's a good idea to google your subject line and see if your answer already exists. — Tab Alleman, Apr 23 '18 at 17:37
Perhaps a typo, but declaring a varchar without a precision will give you only 1 character. — John Cappelletti, Apr 23 '18 at 17:42
Further proof of why it is so important to specify varchar length. As a variable the default length is 30, but when being used as a parameter the default length is 1. Nothing like defaults changing based on usage. Safest by far is to be explicit and always define the length. — Sean Lange, Apr 23 '18 at 18:10

score 2 · Accepted Answer · answered Apr 23 '18 at 17:39

You can create a function like the below one, and call it like this:

INSERT INTO TableName (val)

Select val
FROM dbo.splitDelimitedInotTable('name1,name2,name3,name4', ',')

CREATE FUNCTION [dbo].[splitDelimitedInotTable](
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END

Save a string array into SQL database

1 Answers1