loading an array into a 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 2000 Forums

SQL Server Development (2000)

loading an array into a table

Author

Topic

slim
Starting Member

3 Posts

Posted - 2005-01-18 : 04:54:29

Hi,
I am trying to load an array from a column in 1 table to another table
where each element of the array will have it's own column ie:
Table A
Col1
1 0 0 33 0 0

I need the data to be split into seperate cols :
Table B
Col1 Col2 Col3 Col4 Col5 Col6
1 0 0 33 0 0

Now I can do this using dynamic SQL and a cursor but the problem is that I have several million rows, so ideally I would like to use a set-based operation. I have looked at table functions but the problem there is using a table as a parameter doesn't seem to work.
Anyone got any ideas?
Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-18 : 05:07:13

see
http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html

and
insert tblb
select
dbo.f_GetEntryDelimiitted(col1, 1, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(col1, 2, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(col1, 3, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(col1, 4, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(col1, 5, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(col1, 6, ' ', 'N')
from tbla

This will be quite slow for that number of recs. Try it and see.
You can make it more efficient by using a temp table to hold the position so you don't start from the first entry in the row each time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-01-18 : 06:23:52

BCP OUT the column into a file and then BCP IN the file into the second table with the column delimiter as ' '. This could be much faster for your several million rows.

hth.

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

slim
Starting Member

3 Posts

Posted - 2005-01-18 : 06:30:38

Thanks for the replies lads,
NR - looks interesting I will take a closer look.
Hgorijal, I know that works, the problem is tight security in my current environment. However if all else fails, then I'll have to push that solution.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources