Can I use cursors here?

Question

So here is the starting point

CREATE TABLE #Data (  
  ID      INT IDENTITY(1,1),  
  MyData  VARCHAR(200)  
)   

INSERT INTO #Data (Data) VALUES ('Test123, Test678')  
INSERT INTO #Data (Data) VALUES ( 'abcd, efgh, mnop')  

I want to parse the data, separated by commas, from the MyData column and associate them with the corresponding identifier. So the end result will be

ID  ParsedData
--------------
1  Test123  
1  Test678  
2  abcd  
2  efgh  
2  mnop  

I can do this with cursors, but want to avoid it. Is there a better way to write a query?

Answer 1

The best way to do this is a matter of much controversy , and also depends on the length of the string, the frequency of the delimiters, the simultaneous use, compliance in parallel plans, whether the results will be used in the operation JOIN...

Erland Sommarskog does some performance tests here .

split Adam Machanic TSQL , . .

1.

2.

SELECT ID,OutParam 
FROM #Data
CROSS APPLY dbo.SplitString(MyData,',')

Answer 2

proc? , ;

CREATE TYPE [dbo].[SomeInfo] AS TABLE(
[Id] [int] NOT NULL, 
[SomeValue] [int] NOT NULL )

proc ;

CREATE PROCEDURE [dbo].[AddSomeStuff]
    @theStuff [SomeInfo] READONLY
AS
BEGIN
    INSERT INTO SOMETABLE ([...columns...])
    SELECT [...columns...] from @theStuff
END

.net, datatable ( ), proc ;

var cmd = new SqlCommand("AddSomeStuff", sqlConn) {CommandType = CommandType.StoredProcedure};

var param = new SqlParameter("@theStuff", SqlDbType.Structured) {Value = table};
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

Answer 3

SQL Server 2005 , - .

, . , - :

SELECT 
d.ID, Value
FROM 
#Data AS d
CROSS APPLY
[dbo].[SplitDelimited](d.MyData, ',') 

- :

ID   Value
 1   Test123
 1   Test678
 2   abcd
 2   efgh
 2   mnop

Answer 4

How to use this feature.

http://www.techrepublic.com/blog/datacenter/convert-a-string-array-to-tabular-data-in-sql-server/375