RE: Need advice on a Data Import Routine

From: Doug R (DougR_at_discussions.microsoft.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 09:39:08 -0800

Thanks for the reply. Regretably I have no control over the form of the
input data or I would be using XML. The data is being sent to us from an
outside business.

Are you familiar with ADO.NET for doing bulk inserts? I would be very
interested in hearing the Pros and Cons of Stored Proc vs ADO.NET.

An alternative would possibly be something that parses the import data file
into XML before the insert. Any ideas on how this could be done efficiently?

"Alejandro Mesa" wrote:

> Doug,
>
> I have heard that sql server is not good for parsing routines, so this can
> lead you to do it in your client app.
>
> What about using XML to represent this complex hierarchal data?
>
>
> AMB
>
> "Doug R" wrote:
>
> > Hello,
> >
> > I have a system that I am writing to automaticly import Credit Transaction
> > data into a SQL Server 2000 Database. I am using a VB.Net application to
> > detect when the file arives and prep it for parsing. The file is
> > aproximately 10Mb of relatively complex hierarchal data that is defined by 2
> > character tokens at the begining of each data line. The structure breaks out
> > into aproximately 6 parent-child related tables with numerous lookup tables
> > on each primary table.
> >
> > The previous aproach loaded each line of raw data into an Import table and
> > used a Stored Procedure to break down the information into the tables. The
> > Stored Procedure is large and very complex. It also has the problem that the
> > Parsing takes so long I need to implement a very long timeout on my
> > connection or I get errors. It is also difficult to track progress through
> > the Import for the user to see.
> >
> > The options I see at this point are;
> >
> > 1. Use the current approach of loading the raw data into an Import table and
> > Parsing with a Stored Procedure.
> >
> > 2. Write the Parsing Routine in VB.NET and use ADO.NET to load the data into
> > the apropriate tables.
> >
> > 3. Use a mixed aproach of breaking the massive Stored Procedure into smaller
> > procedures and managing the steps with VB.Net.
> >
> > I am looking for any tips and advice that the guru's out there are willing
> > to give. Especially a comparision of the effiency of doing ADO.NET bulk
> > inserts vs. Stored Procedure parsing.
> >
> > Thanks for any help given.
> > Doug
> >
> > PS - Sorry for the cross posting in dotnet.general but I am hoping to get a
> > balanced set of views on this issue.

Relevant Pages

  • Need advice on a Data Import Routine
    ... The structure breaks out ... used a Stored Procedure to break down the information into the tables. ... Parsing takes so long I need to implement a very long timeout on my ... Use the current approach of loading the raw data into an Import table and ...
    (microsoft.public.dotnet.general)
  • Need advice on a Data Import Routine
    ... The structure breaks out ... used a Stored Procedure to break down the information into the tables. ... Parsing takes so long I need to implement a very long timeout on my ... Use the current approach of loading the raw data into an Import table and ...
    (microsoft.public.sqlserver.programming)
  • Re: Invoking a form that was created on a separate thread
    ... Use XML on a background thread and be on your own in terms of best practise and common supported scenarios ... I actually managed to get it all working now with my controller class ... and then a proper progress bar is shown during the parsing. ... conventional purposes and just needed to report progress to the UI. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Shredding XML
    ... to determine which field values should be extracted from an XML doc ... The stored procedure works, but the process of dynamically gathering ... DECLARE keycolumns_cursor CURSOR LOCAL ... DECLARE @xPath varchar ...
    (microsoft.public.sqlserver.xml)
  • Re: Making classes from Metaclasses globally available
    ... > Catch is, said text files are structured much like XML, but they're NOT XML. ... > be looking for and then parsing. ... > basic schema parsing and class creation ... > I'm thinking the parsing functionality will be extra polated from the schema ...
    (comp.lang.python)
Loading