Problem with Exists | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with Exists

Hello everyone<br /><br />I want to write a query with exists<br /><br />1.Select * from Cities<br />2.Select * from CDS.H34.Dbo.Cities<br />3.Select * from cities where exists (Select * from CDS.H34.Dbo.Cities)<br /><br />I have written above 3 queries. the table structure for Cities table in both the databases is same. <br />When i excute query 1 :I get 2225 recrods, <br />for the query 2 :I get 6 recrods. <br />So logically when i execute query 3 i should get 6 recrods, but still i m getting 2225 recrods.<br />Can any one tell me whats wrong with the query.<br /><br /><br />What i want to do is, i want to check whether the recrod exists in CDS.H34.dbo.cities (Linked server) and if not i will insert the recrod else update the recrod. Also I am Passing the Linked server and database name ie. ‘CDS.HT34.dbo.’ dynamically<br /><br />This is the code that i have written<br /><br />SEt @sqlcmd = ‘If Exists (select Id from ‘ + @dbname + ‘cities where exists (select * from Cities where Id = @Id)) Set @Flag = 1 else Set @flag = 2′<br /><br />exec sp_executesql @sqlcmd , N’@ID int, @Flag int out’ , @Id , @flag out<br /><br />Print @flag<br /><br />The recrod for Id = 1 is present in Cities but not present in CDs.H34.dbo.cities. So for Id = 1 the @flag should be 2, but it is printing 1[?]<br /><br />Pls Help to find what is going wrong<br /><br />Regards!<br />Pallavi[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]
I think EXISTS may be the wrong approach…EXISTS will check to see if there are any rows whatsoever returned by the second query, and if there is, will return everything from the first regardless of whether it is in the second. You need to have something to link the data returned by the second query to your main one. Not sure exactly how to do it without seeing your table definitions (select * doesnt really help!) Cheers,
Ben ‘I reject your reality and substitute my own’ – Adam Savage
EXISTS is not a magic word, so you have to spell out the criteria in a WHERE clause. The missing link for you is that, within the subquery, you can refer to columns from the main query as well (correlated subquery): SELECT T1.* FROM dbo.Cities T1
(SELECT * FROM CDS.H34.Dbo.Cities T2
WHERE T2.Id = T1.Id) By the way, this is equivalent to: SELECT T1.* FROM dbo.Cities T1
INNER JOIN CDS.H34.Dbo.Cities T2
ON T1.Id = T2.Id The NOT EXISTS version is equivalent to: SELECT T1.* FROM dbo.Cities T1
LEFT JOIN CDS.H34.Dbo.Cities T2
ON T1.Id = T2.Id

Hi Ben thanks for the reply, the table definations are same on both the databases.
CREATE TABLE [dbo].[Cities] (
[ID] [int] NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[Keywords] [nvarchar] (500) NULL
GO this defination is same for both the tables. I am using the same databse structure, just the physical location is diferent. Do u have any other idea how this can be done. I want to use it for other tables also otherwise I would have used joins. but this would not be possible for the tables with more no of columns. Thanks Ragards! Pallavi

Okay, looking at the table definition, the subquery/join should probably be based on the City column, not on the Id column.
Hi Adriaan For cities table it will be ok to use joins as you have selected but my other table contains 27 columns, so giving he matching criterion for all those column will be difficult as well as it will affect the prformance. Pls correct me if i am wrong. I just want to check whether the selected row from source database exists in the destination databse. I want to check for all the fields. Just by checking the id i.e the primary key from the respective table will give me whether the recrod in the destination db for this id is present or not For example
The recrod in cities
Id City Keywords
2 aaa
3 bbb now while inserting the record in destination db i will check whther the CDs.H34.dbo.cities contains recrod with id = 2 or not if not then i will insert the record with id =2
but after some time my recrod in the source db changes Id City Keywords
2 aaabbb now if i chack the recrod with id = 2 is present in destination db, so i will not insert the recrod, but the vaule undere city wolumn will be aaa but i want it to ne aaabbb. do u know any other way, of doing this? Regards! Pallavi
This only works if you have a pre-defined meaning for the ID value – in my reality a City is called "Amsterdam", and it doesn’t change to "London" overnight. There may be a spelling error in the name, of course. You can use a substitute key, like you do, but in that case you must have a common starting point in both databases, and people on both ends who want to add a city must decide who will add the new City. If the same City is added to both databases, and the ID is an identity column, then there is no way to guarantee that the ID values will correspond between the databases – and there is no easy way to reconcile the differences. But if you have the data entry sorted out, then yes – you can use the ID column, as shown in the queries on my first post on this thread.
Thanks Adriaan, as far as the Id column is not autoincrement, The CDS is Centran db server, so we have to keep the id’s consistant in both db’s. so for all the tables we have not used autoincrement for both the databases. I know that the recrods will not change overnight, but it may change. so we have to make sure that the changes are moved to Central server. The data enty is done in H34 and then the data is checked for sonsitancy by applying som rules. if the data is ok thru the check then only we put that data on the central server i.e cds. Also we have to keep track of the data changes. for that purpose we are usging UpdationDate in some of the tables in cds but that date is not there in H34. I wanted that query for checking whether the recrod is begin updated or not. but from these answers i think that i will have to use join only. anyway thanks alot for your suggestions. Regards! Pallavi