CSV list to be saved into different columns

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

Transact-SQL (2000)

CSV list to be saved into different columns

Author

Topic

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-18 : 05:40:21

A colleague (honest) has a CSV generated in a SP, which he is looking to save into seperate columns in a table (he's denormalising some data for presentation purposes (not a good idea I know, but who am I to give advice!)....but it's not working (surprise).

create table abc
(col1 char(2),
col2 char(2),
col3 char(2))

declare @abc varchar(100)
select @abc = '1,2,3' -- this will be dynamically filled...ie not a fixed set of values (and there will be 50+ columns)
insert into abc values (@abc)
insert into abc values (1,2,3)

in essence the 2nd insert above works, whereas the 1st doesn't (variables doesn't match number of cols, etc).
Is there a technique available to get the 1st to work?

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 05:51:14

maybe this?

EXEC ( 'insert into abc values (' + @abc + ')' )

Kristen

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 05:53:22

Actually you might need something a little more robust, something along the lines of:


DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'insert into abc values (' 
               + REPLACE(@abc, '''', '''''')
               + ')'
EXEC (@strSQL)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 05:55:01

It is possible from Dynamic sql


Create table abc
(col1 char(2),
col2 char(2),
col3 char(2))

declare @abc varchar(100)
declare @sql varchar(100)
set @sql=''
select @abc = '1,2,3' 
Set @sql='insert into abc values ('+@abc+')'
Exec(@sql)
select * from abc

Provided you create the enougn columns in the table

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-18 : 06:11:38

Bang on the money guys! Many thanks!
Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources