what is the fastest way to do this

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

what is the fastest way to do this

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2008-05-14 : 11:21:03

right now I have a stored procedure that goes through each of the Line and Body fields using a cursor. The problem is that this method is very slow. How would you experts solve this problem? any Hints or suggestions?



	BEFORE					
EXAMPLE	Part	Line	Body	Series	Engine	Year
1	1234	A,B		W	ETC	1998
2	5678	9	91,93,94,95	W	ET0	1997
3	345656	S,R	5,6,12	W	ENC	1995
						
						
	AFTER					
EXAMPLE	Part	Line	Body	Series	Engine	Year
						
1	1234	A		W	ETC	1998
1	1234	B		W	ETC	1998
						
2	5678	9	91	W	ET0	1997
2	5678	9	93	W	ET0	1997
2	5678	9	94	W	ET0	1997
2	5678	9	95	W	ET0	1997
						
3	345656	S	5	W	ENC	1995
3	345656	S	6	W	ENC	1995
3	345656	S	12	W	ENC	1995
3	345656	R	5	W	ENC	1995
3	345656	R	6	W	ENC	1995
3	345656	R	12	W	ENC	1995

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 11:43:31

[code]DECLARE @Sample TABLE (Example INT, Part INT, Line VARCHAR(20), Body VARCHAR(20), Series VARCHAR(2), Engine VARCHAR(4), Year SMALLINT)

INSERT @Sample
SELECT 1, 1234 , 'A,B', NULL , 'W', 'ETC', 1998 UNION ALL
SELECT 2, 5678 , '9' , '91,93,94,95', 'W', 'ET0', 1997 UNION ALL
SELECT 3, 345656, 'S,R', '5,6,12' , 'W', 'ENC', 1995

SELECT Example,
Part,
l.Data AS Line,
b.Data AS Body,
Series,
Engine,
Year
FROM @Sample
OUTER APPLY fnParseList(',', Line) AS l
OUTER APPLY fnParseList(',', Body) AS b[/code]

E 12°55'05.25"
N 56°04'39.16"