June 22, 2011 at 11:29 am
My Project's Outline:
Parse a file that has up to 1200 characters per line. We have definitions for each character location, but there is a small catch. Character 22 can be 0 to 9, and will decide what format the rest of the line will be. I am taking the first 22 characters, and putting them in a meta data table. I still need to put characters 23 - 1200 into one of ten possible tables (dbo.DataArea_1, dbo.DataArea_2...dbo.DataArea_0).
I know I can step from 1 record to the next with While loop logic, but I'm curious if there is a faster way to do this.
My current logic:
Declare @ProcessPath Char(1),
@LoopCount Int,
@MaxLoopCount Int
----------
Set @LoopCount=1
Set @MaxCount=
(Select Max(ID_Field)
From dbo.ProcessMetaData)
----------
While @LoopCount<=@MaxCount
Begin
Set @ProcessPath=
(Select PathID
From dbo.ProcessMetaData)
----------
If @ProcessPath='1'
Exec dbo.ProcessPath1 -- Parse procedure using SubString into dbo.DataArea_1
--etc.
----------
Set @LoopCount=@LoopCount + 1
End
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 22, 2011 at 1:39 pm
Seems like you could create a table with a single nvarchar column large enough to hold the largest record for the import portion. Then do 9 - 10 insert statements for each of your different record types.
insert table1
from bigOleNastyTable
where substring(col1, 22,1) = '1'
delete bigOleNastyTable
where substring(col1, 22,1) = '1'
insert table2
from bigOleNastyTable
where substring(col1, 22,1) = '2'
delete bigOleNastyTable
where substring(col1, 22,1) = '2'
Not really sure but that might be easier/faster than going rbar.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 7:52 am
CELKO (6/22/2011)
Why do you want to do this in SQL? It was never meant for writing parsers. And you got some data element names wrong.
I don't get a choice in this one. I have been slowly learning VB.NET (I'm an old VB 6 programmer, but it has been years, and .NET is a great deal different), but I don't have enough under my belt to use this option. We also have to have this where we can use it as a stored procedure that everyone at my office has access to, so SQL Server is what I was told we are going to use. Again I don't get a say in this, I just get assigned to the specific tools and told to make them work.
As for the suggestion from Sean, I will try this out. I should be working to complete the project this afternoon, so I'll let you know how it goes.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 24, 2011 at 10:19 am
jarid.lawson (6/23/2011)
CELKO (6/22/2011)
Why do you want to do this in SQL? It was never meant for writing parsers. And you got some data element names wrong.I don't get a choice in this one. I have been slowly learning VB.NET (I'm an old VB 6 programmer, but it has been years, and .NET is a great deal different), but I don't have enough under my belt to use this option. We also have to have this where we can use it as a stored procedure that everyone at my office has access to...
Yikes! Will multiple people will have the ability to call said proc, possibly creating a situation where it is called concurrently?
..., so SQL Server is what I was told we are going to use. Again I don't get a say in this, I just get assigned to the specific tools and told to make them work.
SSIS is part of SQL Server. Could you convince the powers-that-be to use It instead of T-SQL for this? The resulting SSIS package could be setup to be callable from within a T-SQL process by leveraging SQL Agent and the sp_start_job proc. Just an option, because writing this in T-SQL not only makes me :sick: but it will bring much pain into your development experience and any others that follow your work.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 24, 2011 at 1:00 pm
opc.three (6/24/2011)
Yikes! Will multiple people will have the ability to call said proc, possibly creating a situation where it is called concurrently?
Our system is set up so that each project will have it's own database. Our integration team has 4 people in it, so the chances of us calling the proc at the same time in the same database is infinitesimal at best.
SSIS is part of SQL Server. Could you convince the powers-that-be to use It instead of T-SQL for this? The resulting SSIS package could be setup to be callable from within a T-SQL process by leveraging SQL Agent and the sp_start_job proc. Just an option, because writing this in T-SQL not only makes me :sick: but it will bring much pain into your development experience and any others that follow your work.
I agree about SSIS. I am working on the 4th data area format (of 10), and have already written over 2,000 lines of code. I could talk them into SSIS once I set one up and show how effective it is. The 2 issues with that: 1) - I would have to do this very low key since I would develop this without the full go ahead...this is the usual way things get developed here, so this is not that big an issue; 2) - I have next to zero experience with SSIS, so I wouldn't know how to set that up. Currently the T-SQL route is the fastest...at least for me.
I will keep the SSIS package option in mind, and use this as part of my training once I start learning about it.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 24, 2011 at 1:07 pm
Tough spot. It sounds like you know enough T-SQL to get it going, but yeah, 2,000+ lines of procedural T-SQL code is what you get when you do stuff like this in the database. Good luck on that front.
When you get to SSIS start with the Conditional Split Data Flow Transformation. It does exactly what it sounds like it would do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply