Post reply

Import CSV File Data

  • June 7, 2007 at 12:40 pm

    #711499

    David,

    Here is my storedProcedure. 

    CREATE PROCEDURE CSVDump

    AS

     --Bulk Insert Into staging table

    Bulk Insert Staging_CSVDump

    From "C:\CSV.txt"

    With

     (

      FIELDTERMINATOR = ',',

      ROWTERMINATOR = '\N'

    &nbsp

    --Update main table

    UPDATE tblMain

    SET Prize = Staging_CSVDump.Prize,

        Quantity = Staging_CSVDump.Quantity,

     FROM  tblMain INNER JOIN

          Staging_CSVDump ON tblMain.OrderNo = Staging_CSVDump.OrderNo

    GO

    Now, how can i schedule this to run twice a day ? I do not have job schedule option.

    Thanks

  • June 7, 2007 at 3:43 pm

    #711542

    Sohil, I'm not sure if I could easily walk you through job creation without posting a bunch of screenshots, etc., so I'll show you where to look. In SQL Server Management Studio, Click on Help, then Index, and in type "jobs" in the "Look for:" textbox. Click on the listing that says "jobs [SQL Server Agent]" and have some fun.

    By the way, your stored proc doesn't yet truncate the staging table, so you'd currently be appending the text file to it every time you run it. From your earlier posts, I'm guessing you want it truncated at the start of this stored proc.

  • June 8, 2007 at 7:28 am

    #711669

    David,

    I did added the "Delete From Staging_CSVDump" line at the end of procedure.

    I will look into online help for job creation.

     

    Thanks

     

  • June 14, 2007 at 8:11 am

    #712943

    David,

    Part of my stored procedure inserts data from staging table to main table.  When entering data into main table is it possible to add "$" symbol for one of the field and then enter data ?

     

    Thanks

     

  • June 14, 2007 at 9:51 am

    #712995

    Thanks,

    I figure it out

  • June 14, 2007 at 5:11 pm

    #713078

    Please tell me you didn't add a dollar sign to some numeric data and store it as a varchar...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 15, 2007 at 7:17 am

    #713242

    Jeff,

    Field is define as Varchar Datatype

     

    Thanks

  • June 15, 2007 at 8:41 pm

    #713435

    Are you storing numbers that you will calculate with in that column??? World of hurt getting ready to happen there... $ won't let you do calcs unless you first convert to MONEY and you probably have nothing in place to keep other non-numeric characters out of the column.  Also, code will always be slower when doing math because of the implict conversions that will be done.

    Like I said, "Please tell me you didn't add a dollar sign to some numeric data and store it as a varchar..."  That's just so wrong on so many fronts... and I haven't even mentioned them all...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 18, 2007 at 6:53 am

    #713546

    Jeff,

    I'm only storing $ symbol in field (varchar) for reporting.  There will not be any calculation.

    Thanks

     

  • June 18, 2007 at 7:09 am

    #713552

    Ok... whatever... that's still not the way to do it... the $ sign should be added in a SELECT (if you've gotta do it that way)... in truth, the formatting should be done by the GUI, if you have one.  Really bad practice to store formatted info in SQL for more reasons than I could list in a simple post.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 16 through 24 (of 24 total)

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