Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    18

    Question Unanswered: How to update record & insert the record?

    Hello,

    I am a beginner sql 2000 user, I would like to have your help, please.
    I need to import txt file to sql 2000 table daily, so I created DTS package.
    I want to update the existining record and also insert the record if there are any changes in the input txtx file. Where in DTS package, I can have these conditons? Could someone show me some example code?

    I appriciate your help! Yanoroo

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    I want to update the existining record and also insert the record if there are any changes in the input txtx file.
    Do you mean that you have a text file to import that reconciles against an existing database and only update records that currently exist and insert new records ? Where is this data originating - from another db ... ?

  3. #3
    Join Date
    Dec 2003
    Posts
    18
    The text file is in server (shared server by the group). The file is refreshed by the mainframe job daily. The text file contains last three months of data - most records remain same but some fields in some records maybe updated daily.
    I need to import the text file daily to refresh the table.

    Did I answer your question? Thank you, Yanoroo

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CREATE 2 staging tables

    A Today table and a yesterday table..they both should be identical and both should match the file...

    To start, load yesterday with the data in the database
    DTS the new file in to the today folder..

    Then join the tables

    first to outer joins...

    any data in today not in yesterday...thats a delete
    any data in yesterday not in today...thats an insert
    any data in both, that's an update

    DAY 2

    TRUNCATE yesterday
    Insert all rows from today into yesterday
    TRUNCATE TODAY
    load Today from the file

    Do the compares...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    18
    Thank you for the information.

    I'll try, thank you again. Yanoroo

Posting Permissions

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