Programming in sql

Question

Post reply

Programming in sql

@DJ

SSCommitted

Points: 1989
More actions
February 8, 2005 at 7:47 am

#92229

Hi

I have a visual basic code that reads a long string from an external database and then extracts data by use of substring. The result is inserted into a new sql database.

Something like this:

Read * from external databse.workinghours

for i = 1 to 356

HoursaDay = substring(<longstring> i*4, 4)

insert into calender (date,hours)

next

I want to discard the visual basic code and rather get sql to do the whole procedure it self. Is this possible to do in sql?

If have tried to figure out how to make sql iterate, but so far no luck

Hope you have ideas about this.

Best regards

DJ

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

tal mcmahon-202603 SSC-Addicted Points: 474 More actions · Answer 1

tal mcmahon-202603

SSC-Addicted

Points: 474

February 8, 2005 at 8:27 am

#541133

Hi,

I can show you how, but I think having the VB do it is a better solution.

all you need to do is use a While loop not a for next

declare @counter int

set @counter = 1

While @counter < 366

Begin

your parse code here

your insert code

set @counter = @counter+1

End

HTH

Tal McMahon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

CREATE TABLE [Numbers] (

[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,

CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED

(

[PkNumber]

) ON [PRIMARY]

GO

Declare @i as int

set @i = 0

--I use this table for many other string operations like this one and since a varchar can containt 8000 chars I need 8000 numbers

while @i <= 8000

begin

Insert into dbo.Numbers Default values

set @i = @i + 1

end

GO

Declare @Dates varchar(40)

SET @Dates = '1234567890123456789009876543210987654321'

Select SUBSTRING(@Dates, 1 + (PkNumber - 1) * 4, 4) from dbo.Numbers as ExtractedDate where PkNumber <= LEN(@Dates)/4

--now you can simply do something like this :

CREATE PROCEDURE dbo.MyProc @Dates as varchar(8000)

AS

SET NOCOUNT ON

Insert into dbo.MyTable (MyField) (Select SUBSTRING(@Dates, 1 + (PkNumber - 1) * 4, 4) from dbo.Numbers as ExtractedDate where PkNumber <= LEN(@Dates)/4)

SET NOCOUNT OFF

GO

And you'll have this proc insert as many as 2000 records in a single query instead of having vb send 2000 requests on the server (6000 round trips instead of 3).

Please msg me if you need further explanation on how to use this technique.

tal mcmahon-202603 SSC-Addicted Points: 474 More actions · Answer 3

tal mcmahon-202603

SSC-Addicted

Points: 474

February 8, 2005 at 9:17 am

#541156

thats an interesting solution,

i have never seen anyone parse a string like that. nice trick!!

tal_mcmahon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I use to have split function that would parse a string of comma delemited values and return them into a table. It was using a while loop to scan the string but once I compared that method to the set based solution I found that it was at least 5 times slower than the little trick I showed you (when it's not 10-15 times slower). I've never used a while loop since then in SQL to do some strings work.