SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot moretea's Avatar
    Join Date
    Jun 2004
    Location
    Texas, USA
    Posts
    118
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    SQL Server 2005: passing params to SP by reading from a file?

    We already have stored procedures in place to update or insert to a table; what we would like to do now is to pass in an .XLS (or whatever) with the associated parameters, from which SQL Server could read row by row and determine which SP to run. Something like this...

    Code SQL:
    SP      sessionID  facilityID  category  mfr          model          num_units
    UPDATE  10001      100         sedan     Ford         Taurus         3
    UPDATE  10003      105         sedan     Honda        Accord         -2
    INSERT  10010      107         minivan   Chrysler     T&C            2
    UPDATE  10015      110         pickup    Dodge        Ram            1
    INSERT  10020      113         pickup    Chevrolet    Silverado      3
    UPDATE  10022      117         suv       Toyota       RAV4           -1
    ...

    We have about 1000+ rows of data to be either updated or added; surely this can be automated in this way, can't it? TIA for your help!
    Last edited by moretea; Oct 19, 2009 at 17:04. Reason: font formatting mistake

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    How does the data come in? That will really determine what your reading options are.

    Anyhow, I'd probably hit it as a .NET command line application using something like filereader to load the data into a structure then running ADO.NET commands to fire the procedures. Add a little log4net action to handle logging to console/email/whatever and we be chilling.

  3. #3
    SitePoint Zealot moretea's Avatar
    Join Date
    Jun 2004
    Location
    Texas, USA
    Posts
    118
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply! Any recommendations on good reading regarding .NET and filereader on the command line?

    We're not running .NET solutions around here, although I was looking to see if FSOs could be leveraged; plus, my boss is no MS fan. I think we may end up going with the solution at http://weblogs.asp.net/jgalloway/arc...12/442618.aspx. It won't dynamically select which SP to run based on input, but that's not a dealbreaker; we'll just run it once for all the INSERT queries, and once for all the UPDATE queries. (We are handling DELETEs by just setting the number of units to 0.)

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    First, you are running .NET solutions--Sql 2005 is partially built on .NET 2.0.

    I meant to say filehelpers, not filereaders. It can be found @ http://filehelpers.sourceforge.net/. Its a relatively easy to use library for parsing text and excel files. If you can get the data in XML you've got much better options.

    Not sure how Jon's database script helps you--bigger issue is parsing (and dealing with the inevitable errors) the incoming data, not how to throw it at SQL Server.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •