updating some fields with new data

  • I'm new to SQL and have not been formally trained so forgive me if this question is too mundane for you or insults your numerous years of experience:

    I have a table that I am trying to update via a DTS package I have created. My package asks for a tab delimited text file as input and it needs to update only some fields of a table. I basically want the package to do this: "for each id in the text file, if a matching id is found in the table then update fields 2, 4 and 6, skip the rest of the fields, if a matching id is not found in the table then append all the data into the table."

    What I have done so far is to load all the data from the text file into a temporary table. I don't know where to go from there. I'm using SQL Server Enterprise Manager.

    Any tips, tricks, advice, help would be greatly appreciated.

    TIA,

    Jennifer


    TIA,

    Jennifer

  • putting the data from the file in to a table is the way to go. You may have to make the table a permante table to do this but here goes.

    load the data with the dts then use query analyzer to create an update script then use the DTS TASK "Execute SQL Script" to run the update script. Use the "work flow" so the DTS knows what to run first.

     

    For more information on these look at Books online. It is typically installed with SQL Server tools. It will be a great help. If it is not installed you can get it from Microsoft download website or from the SQL server CD.

    Hope that helps. If not let us know.

     

  • Can I do this: after I've put the data into the temp table, can I do a dts task to execute sql script that will run the update and put the data into the permanent table?

    Thanks for your reply,

    Jennifer


    TIA,

    Jennifer

  • Yes the update script run as a DTS execute SQL Script can update the permanent table. However you may have problems using a temp table in the update script. you may need to make a permanent table to use as the dump table. You can then reuse the dump table by truncating the table in a Execute SQL Script before the import script running. If you have trouble accessing the temp table you can try using a global temp table. If that does not work like I said you may need to have a permanent table for the dump table.

     

    Hope that make sense. If not let me know and I will try to explain.

     

Viewing 4 posts - 1 through 3 (of 3 total)

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