Updating data via DTS?

[eao]

I'm new to DTS packages. I want to update a table in a database based with data from another based on a key field. Can I use the import wizard, or do I need to DTS to execute a stored procedure to run the update?

 

[dbomrrsm]

you can do it with an execute SQL task

update tableA
set field2 = tableB.field2
where tableA.field1 = tableB.field1

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[yksvaan]

I prefer to write that code in a procedure and execute the procedure from Execute Sql task. DTS task won't necesserily give any errors if you are violating constraints.

Another thing is that are you using DTS specific capabilities at all, I mean if you are just working with the same sql server as the only data source, you don't need dts, but just sql, like the post of dbomrrsm could be interpreted as well.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[eao]

Initially, the data resides on another database server. My plan was to import the data to a staging table, and then run an update query similar to the above example. I also want to be able to schedule the task to run.

 

[ConBran]

You dont need to import it to a staging area. Just run an Execute SQL task as suggested above by dbomrrsm. You will just need to create two sql server connections, one pointing to each of the databases, it doesn't matter if they are not both on the same server, you can chagne which server you want to point to when you create the connection.

You will have to modify the code from dbomrrsm to be safe. Probably something along the lines of:

update databaseA.dbo.tableA
set field2 = databaseB.dbo.tableB.field2....

Im not terribly good at this myself, so if you try this and it doesn't work, hopefully someone else will have spotted the flaw in my suggestion.

 

[eao]

Do I need to disconnect from the created connections when the package has completed, or does this occur automatically?

 

[yksvaan]

eao, ConBran's approach is possible only if you define a linked server. If not, you doing it right by using staging area. Then run the above sql either in Execute sql Task directly or via stored procedure.

Either way you can't do it with a wizard. You must yourself create a dts package, you need only one connection to your target sql server and add the Execute sql task. That's it.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]