split value & insert to table - SQL Server Forums

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)

split value & insert to table

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-01-23 : 07:42:41

I imported a text contains the following format to a temp table
id col1
101 100000,200000,3000001,400002
102 100000,200001,3100001,400003
103 100001,200010,3100002,400005

What is the best way to break down the col1, and insert into table?
id col1
101,100000
101,200000
101,300001
101,400002
102,100000
102,200001
...,......
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 08:18:42

[code]DECLARE @Sample TABLE (ID INT, Data VARCHAR(30))

INSERT @Sample
SELECT 101, '100000,200000,3000001,400002' UNION ALL
SELECT 102, '100000,200001,3100001,400003' UNION ALL
SELECT 103, '100001,200010,3100002,400005'

SELECT s.ID,
d.p
FROM @Sample AS s
CROSS APPLY (
SELECT PARSENAME(REPLACE(Data, ',', '.'), 1) AS p UNION ALL
SELECT PARSENAME(REPLACE(Data, ',', '.'), 2) UNION ALL
SELECT PARSENAME(REPLACE(Data, ',', '.'), 3) UNION ALL
SELECT PARSENAME(REPLACE(Data, ',', '.'), 4)
) AS d
ORDER BY s.ID,
d.p[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources