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 |
|
tbraga
Starting Member
11 Posts |
 Posted - 2006-11-27 : 12:40:29
|
| Hi All, I am having difficulty writing a semi-advanced / advanced SQL Select Statement. I have two databases on two different servers that I am intergrating using a product through BMC. The integration works but when I run it twice I recieve duplicates because there is no WHERE clause.Both tables on each server have the EmployeeID attribute that I be using my WHERE clause on and I have DSN's setup on the server so they can talk to one another but I am not sure how to connect to that other server and database.Would I create a connection string to the DSN after the 'where not in'? If so, i'm not sure how to go about calling the Magic DSN. This is what I have so far:Select * from usteam.dbo.v_intranet_employeeswhere not in (select employeeID from .......)Any help would be much appreciated, Thanks So Much!Tim |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-27 : 12:46:58
|
| Use Linked Server. Read about them in Books Online.Peter LarssonHelsingborg, Sweden |
 |
|
|
tbraga
Starting Member
11 Posts |
Posted - 2006-11-27 : 14:22:42
|
| Thanks Peso!I have created a Linked Server and I am able to pull information from it. I am having difficulty with the 'Where not in' clause, I have searched online for about 45minutes but can't figure out what might be wrong. Must be a small syntax error that a second set of eyes might pick up. Here is what I have so far:SELECT * FROM OPENQUERY([Dev-Apps], 'SELECT LastName FROM intranet_employees')where not in (select name from _SMDBA_._CUSTOMER_)Thanks so much!Tim |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:09:11
|
| where LastName not in (select name from _SMDBA_._CUSTOMER_)??Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:10:55
|
| ... If that's the case its a pretty "expensive" query - ALL the rows from intranet_employees on Dev-Apps will be retrieved, and THEN compared against a complete list of [name] values in _SMDBA_._CUSTOMER_ ... |
|
|
|
tbraga
Starting Member
11 Posts |
Posted - 2006-11-27 : 15:19:34
|
Hi Kristen, I was two minutes ahead of you with the fix, looks like we think alike  Yea its pretty expensive but i need the comparison so its not inputting duplicates in the Customer table. Thanks!Tim |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-27 : 15:34:07
|
| This would be betterWHERE NOT EXISTS (SELECT * FROM _SMDBA_._CUSTOMER_ WHERE name = LastName) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:35:30
|
| "Yea its pretty expensive but i need the comparison so its not inputting duplicates in the Customer table"It would be better to employ some sort of "last changed date" column on Dev-Apps...intranet_employees so that you only need to concern yourself with changed data. (You'll have to worry about deleted rows too, of course, but on many systems deletions simple "don't happen" [e..g they get flagged as Deleted/Inactive/Obsolete/Superseded instead so with a bit of luck that is your scenario too!Kristen |
|
|
|
|
|
|
|
|
SQL Select Statement Between Servers