Compare temp table to real table

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Compare temp table to real table

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-31 : 06:29:16

I have a table and a matching temp table. I need to load some data from a text file into the temp table and check that any of the rows do not exist in the permanent table. Could anyone show me the best way to do this please.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-31 : 06:33:13

Select columns from yourTempTable T where not exists
(select * from PermanentTable where col=T.col)

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-31 : 10:37:48

Can anyone tell me why I get:
Line 4 incorrect syntax near ',',

from
SELECT campaignserial
FROM SLNAPP01.StagingDB.dbo.campaigns A
where not exists
(SELECT* from SLNAPP01.e rm.dbo.campaigns B)

Antonio
Posting Yak Master

168 Posts

Posted - 2005-10-31 : 12:02:33

quote:
Originally posted by p.shaw3@ukonline.co.uk

Can anyone tell me why I get:
Line 4 incorrect syntax near ',',

from
SELECT campaignserial
FROM SLNAPP01.StagingDB.dbo.campaigns A
where not exists
(SELECT* from SLNAPP01.e rm.dbo.campaigns B)

Your query is not performing as a correlated subquery! A correlated subquery is a select statment nested inside another SELECT statment statement, which contains a reference to one or more columns in the outer query. In your example, you need to reference all columns which consitiute your primary key in the real table.

Your code will work if you modify it as follows :


SELECT     campaignserial
FROM        SLNAPP01.StagingDB.dbo.campaigns as A
where not exists (SELECT* 
                   from SLNAPP01.e rm.dbo.campaigns as B
                  WHERE A.PK = B.PK)

Also you should note that what your correlated subquery returns in this instance is not that important as you outer cquery is checking for a true or false condition by using the EXISTS operator rather than the actual contents of the correlated subquery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources