Post reply

text file with inserts and updates

  • September 5, 2006 at 4:38 pm

    #94361

    Hello,

    Runnig SQL Server 2000 SP3a on Windows 2003 Server STD

    I have 2 files which get uploaded to my server once per day. Each file contains approx. 10000 insert / update statements.

    I am currently manually loading these into Query Analyzer and executing them to get the data into my db. I would like suggestions on a way I can automate this task.

    Thanks in advance for any ideas.

    N

  • September 5, 2006 at 5:38 pm

    #658671

    Without knowing the details, it sounds like DTS should do the job.

  • September 5, 2006 at 5:50 pm

    #658672

    This seems like a problemmatic approach.

    For starters, you have to trust the validity of these insert/update statements to perform what they say they're going to perform. You wouldn't know if in the middle there was a nested DROP DATABASE command. Not to mention that if you execute 10,000 inserts or updates you're introducing performance issues into the database.

    In the past I've loaded large files into load tables via DTS and/or BULK INSERT. From there I execute a stored procedure which does a compare on a key value to determine if the data already exists within my existing tables or not. I do an update on any existing data and insert the new data accordingly. You have fewer total transactions meaning better performance (instead of 10,000 individual transactions, you have a few transactions... much better on performance.)

  • September 6, 2006 at 7:50 am

    #658737

    I do trust the validity of the statements coming in, because I control the scripts that dumb the data out and generate the text files. Let me give a little more background on what is going on.

    I have a mysql DB residing on a different server (Solaris) that gets updated in real time. Some of the records in that DB get changed, and new ones are added frequently.

    I then have a MS SQL server running that has two databases: a staging DB and the real DB that my application uses. I cannot insert/update directly into the real DB, due to several issues, most important being that the software application must generate a specially formatted GUID (which is an MD5 type hash).

    So I update the staging DB with my data, and there are built-in routines in the software application that handle sync'ing properly from the staging DB to the real application DB.

    Also, due to the nature of the data, I do fresh inserts and updates each time (once a day) that I sync my text files. (ie. I truncate/empty the table in the staging DB and insert all new records). Then let the application sync to the staging DB.

    This process only happens once a day, and there is no other load on the system other than this sync process. The only other access to the system is a web application (accessable by about 15 users, never more that a few at the same time) that pulls some data from the real application DB.

  • September 6, 2006 at 8:07 am

    #658745

    If you have valid SQL statements in the the text file you can execute it by using one of the ways:

    1.Set up a DTS to load the Text file in to Staging table(Temp table with 1 large varchar column and 1 identity column). Loop through the table and build dynamic SQL from the value in the varchar column and execute it.

    2.Create a VB application and read the data from the file using Filesystem objects and after creating the SQL Statement from the content of the file excute it.

    Hope this helps.

    Thanks

    Sreejith

  • September 6, 2006 at 8:40 am

    #658756

    Thanks for the suggestions. I am somewhat of a novice at VB, so that approach may not work for me. I was wondering if there was a way I could automate what I do within query analyzer, where I load the sql file and execute it as-is.

    Thanks.

  • September 6, 2006 at 10:57 am

    #658809

    you can use approach 1 that should work

  • September 6, 2006 at 12:45 pm

    #658855

    Another option would be to create a batch file that runs osql.exe.

    The batch file would accept the name SQL file as a commandline parameter.

  • September 6, 2006 at 1:22 pm

    #658865

    osql worked perfectly. I had previously tried isql, but it had generated an out of memory error.

    I will now create a batch file / script to run osql to import the data.

    THANKS!!! for the assistance

    N

  • September 6, 2006 at 7:14 pm

    #658925

    Just a reminder to everyone that may try... ISQL is deprecated when compared to OSQL.  OSQL gives you the full functionality of SQL Server... ISQL does not.  Recommend that you never use ISQL...

    --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)

Viewing 10 posts - 1 through 9 (of 9 total)

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