SQL Server Performance

updating table

Discussion in 'SQL Server DTS-Related Questions' started by v1rtu0s1ty, Aug 29, 2005.

  1. v1rtu0s1ty New Member

    hi everyone,

    Can someone tell me how to do this in dts? I have 2 tables, source and target table. What I want to happen is pull data from source table then update a specific row in the target table. I can do this using regular sql as a job. But I would like to learn how I can do this using DTS.

    Thanks in advance!
  2. Akthar New Member


    You can use DTS Designer:

  3. Akthar New Member

    Here are the steps:
    On your designer:
    add a Source Connection + Desxtination Connection
    add between a DTS transformation Tasks, choose source and then destination.
    Edit the Transformation tasks and add the field(s) you wwant to update.

    See SQL Books Online , a lot of info. is available.

  4. dineshasanka Moderator

    But his problem is not with fields but with rows. He wants to select row with where condition
    Am I Correct v1rtu0s1ty??
  5. Akthar New Member

    If i;ve well understand, what you can still do with DTS is configure your source connection with a where criteria and the same for the dest.

    but does the DEST have to be dynamic depending on the source[?]

  6. dineshasanka Moderator

    Yes I do have the same question?? Need to do bit of searching
  7. v1rtu0s1ty New Member

    Yes dineshasanka, you are correct. I have a SELECT query with different conditions. And columns on source and target table is not one to one.<br /><br />To make it much clearer, source table has 7 columns while target table has 100 columns.<br />Source table fields are loanno, address and so on. Target table also has a loanno. Loanno field is unique for both tables. That means that if loanno 1234567890 is in source table, update the 6 other fields where loanno 1234567890 in target table is found.<br /><br />I'm hoping that i was able to explain it clearly. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  8. mmarovic Active Member

    Copy rows into auxiliary table then Use ExecuteSQL task.
  9. v1rtu0s1ty New Member

    Hi mmarovic,

    Thanks for the tips. But how do I do that? It's the first time I heard auxiliary table.
    Can you give an example please?

  10. Adriaan New Member

    You create a permanent "holding table" in your database with all the necessary columns for holding the data. In your DTS package, you upload the data into this table, then you merge the data from the table with your target data (preferably in a stored procedure) and finally you delete all data from your holding table.

    You can add a "session id" column if there can be more than one import going on at the same time.
  11. v1rtu0s1ty New Member

    So that means, I can write the SQL UPDATE statement and put it in a stored procedure. Then just have it executed via activex like this code I wrote to insert data from XML into a temporary table.<br /><br />objADOCnn.Open "PROVIDER=SQLOLEDB;SERVER=mssql1;UID=xmlgw<img src='/community/emoticons/emotion-4.gif' alt=';P' />WD=xmlpass;DATABASE=wmdb;"<br /><br />Set objADOComm.ActiveConnection = objADOCnn<br />objADOComm.CommandText = "sp_Insert_ernst_Openxml"<br />objADOComm.CommandType = adCmdStoredProc<br />objADOComm.Parameters.Refresh<br />objADOComm.Parameters.Item(1).Value = XMLResponseString<br />objADOComm.Execute<br />objADOCnn.Close<br /><br />Then, I can just put the same lines after the code above but pointing to the newly written stored procedure <br /><br />.<br />objADOComm.CommandText = "sp_update_wmdb_using_ernstdata"<br />.<br />.<br />.<br />
  12. Adriaan New Member

    Not sure just what you're doing there - but in any case: finalize the data before you insert it into the Excel sheet.
  13. v1rtu0s1ty New Member

    Nope, it's not an excel sheet. You might have confused yourself with XML as XLS. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />But I got the idea!!! <img src='/community/emoticons/emotion-5.gif' alt=';)' />
  14. Adriaan New Member

Share This Page