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.
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.
INSERT INTO KindergartenStudents
(FK_KindergartenId, StudentName)
SELECT
sbk.FK_KindergartenId,
parsed.txt_value
FROM
StudentsByKindergarten sbk
CROSS APPLY
fn_ParseText2Table(sbk.StudentList, ',') parsed
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句