COUNT (*) QUERY ISSUE - SQL Server Forums

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)

COUNT (*) QUERY ISSUE

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-20 : 16:20:27

Hi I am archiving and deleting data with the following query:

Insert into ProTonga_Reed_archive..tblDiaryDeletedMessage
select * from tblDiaryDeletedMessage
WHERE iMessageToID in (select K_SO_id from sign_ons where k_event_id = @iEventid)
OR iMessageFROMID in (select K_SO_id from sign_ons where k_event_id = @iEventid)

Delete from tblDiaryDeletedMessage
WHERE iMessageToID in (select K_SO_id from sign_ons where k_event_id = @iEventid)
OR iMessageFROMID in (select K_SO_id from sign_ons where k_event_id = @iEventid)

I am doing counts before and after. before the main database before the archive and after on the archive database to check the counts. I have found a discrepancy for one run where the before counts dont match the archive. I am wondering if there could be an issue with my count query:

select count(*) as tblDiaryDeletedMessage_proTonga
from tblDiaryDeletedMessage , sign_ons
where ( iMessageToID = K_SO_id or iMessageFromID = K_SO_id) and k_event_id = @iEventid.

The relationship between sign_ons tblDiaryDeletedMessage is one to many. Can anyone suggest a more accurate way to obtain the counts before and after the archive query.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 17:16:30

The old style CROSS JOIN will produce more records if there are duplicate records in sign_ons!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources