SQL Server import row string to table columns

Question

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?

SQL Server has no concept of "string array". How is the data actually stored? — Gordon Linoff
– Gordon Linoff, Commented Apr 4, 2016 at 13:53
use a split function and then dynamically pivot it's results. — Zohar Peled
– Zohar Peled, Commented Apr 4, 2016 at 13:56
always 28 values -- destination table actually has 30 columns - 28 for the values, one key column, one other column. Value / column amount will not change. — phicon
– phicon, Commented Apr 4, 2016 at 14:00
select "insert into table28 values(" + SUBSTRING(text, 2, LEN(text) -2) + ");" as "sql" from table, run, copy column, paste and run :) — Adam Silenko
– Adam Silenko, Commented Apr 4, 2016 at 14:09

Squirrel · Accepted Answer · 2016-04-04 14:04:13Z

3

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

answered Apr 4, 2016 at 14:04

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sean Lange Over a year ago

I agree that a splitter is a good approach but the splitter you referenced is horribly inefficient. You do NOT need to resort to looping for splitting strings. sqlperformance.com/2012/07/t-sql-queries/split-strings or here. sqlservercentral.com/articles/Tally+Table/72993

Squirrel Over a year ago

those splits CSV into rows. OP required it in column

Sean Lange Over a year ago

Right. But you don't need a loop for that. You can use crosstab or pivot to get them back into columns. This would be a great time for an xml splitter.

gofr1 · Accepted Answer · 2016-04-04 14:24:07Z

1

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)

edited Apr 4, 2016 at 14:24

answered Apr 4, 2016 at 14:05

gofr1

16k11 gold badges47 silver badges56 bronze badges

Collectives™ on Stack Overflow

SQL Server import row string to table columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related