the ole csv issue - 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)

the ole csv issue

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-02-18 : 12:35:28

Okay, I have a huge string that is being passed to a sproc. It will contain a list of ids to be inserted into a table. the alternative is nearly 1000 calls to a sproc from an asp page (not the way I want to go)

So, by passing the id list into the sproc, I can split it up and do the lifting in the sproc.

csv = "4, 66, 2, 90, 76, 54"

The question is, is there a way to do this without getting into cursors? and if so, how?

thanks.

________________________________________________

Sprocs are for kids.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-02-18 : 12:40:08

Can the string be stored in a holding db?
Then use bulk to inport into your db.

Jim
Users <> Logic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-02-18 : 12:52:35

it could be done that way I spose'

my brain finally turned on. For this case
I will just use a while loop with charindex (and left and right)
to chop the string up and do the inserts.

the most important thing from my perspective is avoiding 1k of ado calls from the asp page. Otherwise Performance isn't an issue.

I am having trouble excepting the fact that I have to miss happy hour tonight.

________________________________________________

Sprocs are for kids.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-18 : 12:53:00

the easiest way, IMHO, if it is just a list of ID's or numbers, is to use a fixed length string instead of CSV. CSV is much harder to parse than if you define a fixed width -- say, 8 characters -- for each ID and padd them with spaces. All you need is a table of numbers in your DB ( no cursors!) and parsing is extremely easy + quick.

More info at my blog if you are interested:

http://weblogs.sqlteam.com/jeffs/archive/2004/10/04/2167.aspx

- Jeff

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-02-18 : 12:57:33

cool,

that makes sense. life is a csv string. Nothing is fixed.

________________________________________________

As the only Republican that likes the Green Party.<BR><BR>I am contradicting myself.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 12:59:43

[url]http://sqlteam.com/item.asp?ItemID=637[/url]

rockmoose

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-02-18 : 13:15:36

yeah.
here is what I did.
its passing from a select list so there is the space after the comma

While(len(@csv) != 0)
BEGIN
if(isNumeric(@csv) = 1)
BEGIN
Set @mbrid = @csv
Set @csv = ''
END
else
BEGIN
Set @cd = CharIndex(', ',@csv)
Set @mbrid = left(@csv,@cd-1)
Set @csv = Right(@csv, (len(@csv) - (@cd+1) ))
END
Select @mbrid
END

I am no sql server guru so this probably has some performance problems, but it works, and for this app, good enough.

________________________________________________

As the only Republican that likes the Green Party. I am a contradiction.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 16:49:55

That's very good.
Performacewise if you are going to do in the order of 1000 inserts into a table, don't do it in a loop though.
If you can do it in one batch it will be much much much much faster.

Here is one way you can get all the numbers in the csv in one go, without a loop.

-- first you need a numbers table
set nocount on
create table numbers(nr int not null identity(1,1) primary key clustered)
while (select count(*) from numbers) < 50
insert numbers default values
--select * from numbers

declare @csv varchar(8000)
set @csv = '4, 66, 2, 90, 76, 54'

-- retrieve the values from the csv in one set
select
	cast( substring(@csv,fromix,toix) as int ) as mycsvnumbers
from
	(
		select
			charindex(',',','+@csv+',',nr) as fromix,
			charindex(',',','+@csv+',',nr+1) - charindex(',',','+@csv+',',nr) - 1  as toix
		from
			numbers
		where
			substring(','+@csv+',',nr,1) = ','
			and charindex(',',','+@csv+',',nr+1) - charindex(',',','+@csv+',',nr) - 1 > 0
	) as ix

--drop table numbers

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 16:55:03

Oh, and btw:

[url]http://weblogs.sqlteam.com/jeffs/archive/2004/01/25/783.aspx[/url]

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-18 : 17:39:22

fyi -- The fixed length items in the string is, by far, the fastest and easiest method. keep it simple! Not sure why any other method is needed/desired in this case. Especially XML!

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 17:50:39

I feel ashamed for posting the xml link now.

Yes I like the fixed lenghth approach, very nice

I guess vivaldi has some options now....

rockmoose

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-18 : 21:22:36

For completeness' sake, there's also:

http://www.sqlteam.com/item.asp?ItemID=2652

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-02-19 : 11:03:55

quote:
Originally posted by robvolk

For completeness' sake, there's also:

http://www.sqlteam.com/item.asp?ItemID=2652

Wow, lots of good stuff. I knew I had read an article on this before, just couldn't find it (friday syndrome). The process I wrote is a bit slow, so if I get the chance, I will rebuild it.

thanks all.

________________________________________________

As the only Republican that likes the Green Party. I am a contradiction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources