-- make some sample data, about 100,000 rows

DECLARE @variable TABLE (OneRowOfData VARCHAR(200))

INSERT INTO @variable (OneRowOfData)

SELECT '12345|name|domaine|category|2010/11/15|2011/10/30|2|' UNION ALL

SELECT '12346|name|domaine|category|2010/11/15|2011/10/30|2|' UNION ALL

SELECT '12347|name|domaine|category|2010/11/15|2011/10/30|2|'

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable

-- use varchar for all columns in the staging table,

-- reduces chance of failure due to impossible implicit conversions

CREATE TABLE #ListTable

(

listIdToAdd VARCHAR(10)

, nameToAdd varchar(350)

, domaineToAdd varchar(350)

, categoryToAdd varchar(350)

, dateBeginToAdd VARCHAR(10)

, dateEndToAdd VARCHAR(10)

, rankToAdd VARCHAR(10)

)

-- About 5 seconds for 100,000 rows for the SELECT, add two or three seconds for INSERT

INSERT INTO #ListTable (listIdToAdd, nameToAdd, domaineToAdd, categoryToAdd, dateBeginToAdd, dateEndToAdd, rankToAdd)

SELECT

listIdToAdd= LEFT(v.OneRowOfData, Pos1.n-1),

nameToAdd= CASE WHEN Pos2.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos1.n+1, Pos2.n-Pos1.n-1) END,

domaineToAdd= CASE WHEN Pos3.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos2.n+1, Pos3.n-Pos2.n-1) END,

categoryToAdd= CASE WHEN Pos4.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos3.n+1, Pos4.n-Pos3.n-1) END,

dateBeginToAdd= CASE WHEN Pos5.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos4.n+1, Pos5.n-Pos4.n-1) END,

dateEndToAdd= CASE WHEN Pos6.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos5.n+1, Pos6.n-Pos5.n-1) END,

rankToAdd= CASE WHEN Pos7.n > 0 THEN SUBSTRING(v.OneRowOfData, Pos6.n+1, Pos7.n-Pos6.n-1) END

FROM @variable v

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, 1)) Pos1 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos1.n+1)) Pos2 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos2.n+1)) Pos3 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos3.n+1)) Pos4 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos4.n+1)) Pos5 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos5.n+1)) Pos6 (n)

CROSS APPLY (SELECT CHARINDEX('|',v.OneRowOfData, Pos6.n+1)) Pos7 (n)

SELECT * FROM #ListTable