So you have a comma-separated list, and now you need to insert it into the database. But the thing is, you need to insert each value in the list into its own table row. So basically, you need to split the list into its separate values, then insert each one of those values into a new row.

T-SQL now has a STRING_SPLIT() function that makes this type of operation a breeze. This function was first available in SQL Server 2016, and is available on databases with a compatibility level of 130 or above (how to check your database compatibility level and how to change it).

Example

Let’s say we have the following comma-delimited list:

Cat,Dog,Rabbit

We can use the STRING_SPLIT() function to separate each value into its own row. Like this:

SELECT value 
FROM STRING_SPLIT('Cat,Dog,Rabbit', ',');

Result:

value 
------
Cat   
Dog   
Rabbit

So we’re halfway there already. In fact, depending on your requirements, this might be all you need.

Insert the Values into a Table

We can take the above code and use it with an INSERT() statement. In other words, we can insert those values into a table, and each list item will reside in its own row. To do this, we can simply prefix the previous code with an INSERT() statement.

Example:

INSERT INTO Animals (AnimalName)
SELECT value FROM STRING_SPLIT('Cat,Dog,Rabbit', ',');

This inserts each value into its own row in the AnimalName column of the Animals table.

Check the Results

To check the results, run a SELECT statement against the table:

SELECT * 
FROM Animals;

Result:

AnimalId  AnimalName
--------  ----------
1         Cat       
2         Dog       
3         Rabbit    

This result assumes that the AnimalId column is an IDENTITY column (incrementing values are inserted automatically with each new row).

The Full Script

Here’s a script that allows you to do the above example in one go. It creates the database, creates the table, inserts the data, then checks the results.

Create the database:

/* Create database */
USE master;
CREATE DATABASE Nature;

Do the rest:

/* Create table */
USE Nature;
CREATE TABLE Animals (
  AnimalId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  AnimalName nvarchar(255) NOT NULL
);

/* Insert data from the list */
INSERT INTO Animals (AnimalName)
SELECT value FROM STRING_SPLIT('Cat,Dog,Rabbit', ',');

/* Check the result */
SELECT * FROM Animals;