Programming in sql
-
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
-
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
-
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) 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.
-
thats an interesting solution,
i have never seen anyone parse a string like that. nice trick!!
tal_mcmahon
-
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.
-
That trick rates in my top 5.
Right up there with Celko's Nested sets Hierarchy.
I cannot wait for a time to implement it. Thanks for sharing
Tal
-
can you send me that top list? I'd love to see a bundle of those neat tricks.
-
Hi guys
Thank you for all your input.
Old Hand, thank you
your code looks a lot like the one I am interested in. I will try to implement it as soon as possible.Will contact you if I have any questions regarding the matter.
Best regards,
DJ
-
HTH
BTW my name is Remi, "Hold hand" only means that I posted between 300 and 399 messages
. It's just like "newbie" and "grasshoper" for newer members. -
For the records, here are some more examples of this parsing trick:
http://vyaskn.tripod.com/fun_with_numbers_in_t-sql_queries.htm
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Remi
Sorry. My mistake, didn`t look at the top to get your correct name.
Regards
DJ
-
NP... it's just that I've seen this mistake like 3 times yesterday and I thaught it might be a good idea to point it out
.Glad I could help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply