SQL Server: Insert Multiple Rows to a table based on a column in a different table

Question

I have a table

CREATE TABLE [StudentsByKindergarten]
(
    [FK_KindergartenId] [int] IDENTITY(1,1) NOT NULL,
    [StudentList] [nvarchar]
)

where the entries are

(1, "John, Alex, Sarah")
(2, "")
(3, "Jonny")
(4, "John, Alex")

I want to migrate this information to the following table.

CREATE TABLE [KindergartenStudents]
(
    [FK_KindergartenId] [int] NOT NULL,
    [StudentName] [nvarchar] NOT NULL)
)

so that it will have

(1, "John")
(1, "Alex")
(1, "Sarah")
(3, "Jonny")
(4, "John")
(4, "Alex")

I think I can achieve split function using something like the answer here: How do I split a string so I can access item x?

Using the function here:

http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

I can do something like this,

INSERT INTO [KindergartenStudents] ([FK_KindergartenId], [Studentname])
    SELECT 
        sbk.FK_KindergartenId,
        parsed.txt_value
    FROM 
        [StudentsByKindergarten] sbk, dbo.fn_ParseText2Table(sbk.StudentList,',') parsed
GO

but doesn't seem to work.

This is a good reference: http://www.sqlservercentral.com/articles/Tally+Table/72993/ — Felix Pamittan, Aug 29 '14 at 00:05
An example of using @wewesthemenace's link for a problem such as this is available at http://dba.stackexchange.com/questions/72292/splitting-a-csv-column-from-select-query-into-multiple-columns/72298#72298 — Pieter Geerkens, Aug 29 '14 at 01:46
@Melissa, I've switched my old accepted answer by my new answer (using cross apply) that I believe is better for future readers. If you don't like the new answer, feel free to switch again the accepted answer. — Zanon, Sep 26 '14 at 03:17

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

Based on this question, I've learned a better approach for this problem. You just need to use CROSS APPLY with your suggested function fn_ParseText2Table.

Sample Fiddle

INSERT INTO KindergartenStudents
  (FK_KindergartenId, StudentName)
SELECT
  sbk.FK_KindergartenId,
  parsed.txt_value
FROM
  StudentsByKindergarten sbk
    CROSS APPLY
  fn_ParseText2Table(sbk.StudentList, ',') parsed

edited May 23 '17 at 12:04

Community

1
1

answered Aug 29 '14 at 04:07

Zanon

29,231
20
113
126

Zanon · Answer 2 · 2014-09-26T03:13:04.413

I've used the function that you suggested (fn_ParseText2Table) and the following T-SQL is working. You can test it with this fiddle: link.

BEGIN
  DECLARE 
    @ID int, 
    @iterations int

  -- Iterate the number of not empty rows
  SET @iterations = 
        (SELECT 
          COUNT(*) 
        FROM
          StudentsByKindergarten
        WHERE
          DATALENGTH(StudentList) > 0
        )

  WHILE ( @iterations > 0 )

  BEGIN

    -- Select the ID of row_number() = @iteration
    SET @ID =
          (SELECT 
            FK_KindergartenId 
          FROM
            (SELECT 
              *,
              ROW_NUMBER() OVER (ORDER BY FK_KindergartenId DESC) as rn
            FROM 
              StudentsByKindergarten
            WHERE
              DATALENGTH(StudentList) > 0) rows
          WHERE
              rows.rn = @iterations
          )

    SET @iterations -= 1

    -- Insert the parsed values
    INSERT INTO KindergartenStudents
      (FK_KindergartenId, StudentName)
    SELECT
      @ID,
      parsed.txt_value
    FROM
      fn_ParseText2Table
    (
      (SELECT
        StudentList
      FROM
        StudentsByKindergarten
      WHERE
        FK_KindergartenId = @ID), 
    ',') parsed
    END
END

SQL Server: Insert Multiple Rows to a table based on a column in a different table

2 Answers2