CODEWITHSUNDEEP
sqlsql-server
phicon
phicon

Reputation: 3617

SQL Server import row string to table columns

I have a table containing the following text per row

"[0,0,0,1,2,4,1,0,0,2,0,0,0,0,847,18207,0,0,0,0,0,0,0,0,0,0,0,0]"

Now i want to insert these 28 values in a table containing 28 columns. I tried a few split functions but these would return only rows.

Any ideas?

Upvotes: 1

Views: 218

Answers (2)

gofr1
gofr1

Reputation: 15977

DECLARE @x XML

;with cte as (
SELECT '[0,0,0,1,2,4,1,0,0,2,0,0,0,0,847,18207,0,0,0,0,0,0,0,0,0,0,0,0]' as col
)

SELECT @x= (
SELECT CAST('<s>' + REPLACE(REPLACE(REPLACE(col,'[','<a>'),']','</a>'),',','</a><a>') +'</s>'AS XML)
FROM cte
FOR XML PATH('')
)


SELECT  t.v.value('a[1]','int'),
        t.v.value('a[2]','int'),
        t.v.value('a[3]','int'),
        ...
        t.v.value('a[28]','int')
FROM @x.nodes('/s') as t(v)

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

using dbo.fnParseString()

INSERT INTO a_table (col1, col2, col3, . . . )
SELECT dbo.fnParseString(-1, ',', str)
      ,dbo.fnParseString(-2, ',', str)
      ,dbo.fnParseString(-3, ',', str)
      ,....
FROM  yourtable

Upvotes: 3

Related Questions