Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SDyke
Starting Member
2 Posts |
 Posted - 2005-10-13 : 17:19:14
|
| I have been successful with DTS packages and various SQL statements. However, I have a new challenge. I have a table in an SQL Server database. One of the columns is employee number and a column for department number(which is not populated) In a remote AS400 file I have the employees number and department number. I want to create a package to connect to remote table and update SQL Server table with department number where the two tables match on the employee number. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-13 : 17:29:38
|
| You might be able to accomplish without using DTS, but instead using OPENQUERY.---------------------------EmeraldCityDomains.com |
 |
|
|
SDyke
Starting Member
2 Posts |
Posted - 2005-10-13 : 17:38:28
|
| Since I am fairly new To SQL Server can you explain the OpenQuery, how I use it with Update, where is it used in my database, can it be scheduled, etc..? |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-20 : 20:09:19
|
| Well, your best source is SQL Server Books Online (a.k.a. BOL). But in short, OPENQUERY is a command that you can use in your FROM clause in order to access data on a different system. It would look something likeSELECT ...FROM OPENQUERY (PointerToMyOtherServer, 'SELECT ... FROM ...')So maybe you would do something likeUPDATE t1SET t1.DeptNum = t2.DeptNumFROM myLocalTable t1LEFT JOIN OPENQUERY(OtherServer, 'SELECT DeptNum FROM myRemoteTable') t2 on t1.eeNum = t2.eeNumWHERE t1.DeptNum IS NULL---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
Updates Form Remote Data