SQL Server Performance Forum – Threads Archive

updating table

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!

Hello, You can use DTS Designer: AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

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. AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

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

ok…
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[?] AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

Yes I do have the same question?? Need to do bit of searching

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. To make it much clearer, source table has 7 columns while target table has 100 columns. 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. I’m hoping that i was able to explain it clearly. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Copy rows into auxiliary table then Use ExecuteSQL task.

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? Thanks!

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.

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. objADOCnn.Open "PROVIDER=SQLOLEDB;SERVER=mssql1;UID=xmlgw<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />WD=xmlpass;DATABASE=wmdb;" Set objADOComm.ActiveConnection = objADOCnn objADOComm.CommandText = "sp_Insert_ernst_Openxml" objADOComm.CommandType = adCmdStoredProc objADOComm.Parameters.Refresh objADOComm.Parameters.Item(1).Value = XMLResponseString objADOComm.Execute objADOCnn.Close Then, I can just put the same lines after the code above but pointing to the newly written stored procedure . objADOComm.CommandText = "sp_update_wmdb_using_ernstdata" . . .

Not sure just what you’re doing there – but in any case: finalize the data before you insert it into the Excel sheet.

Nope, it’s not an excel sheet. You might have confused yourself with XML as XLS. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> But I got the idea!!! <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />

[8D]

]]>