pgaudreau
Posts: 4
Joined: 9.Sep.2008
Status: offline
|
Upon importing from bundles of PST files and doing some subsequent digging in the databases I believe I have duplications of emails in the archive.
Ran a query to find a count of arc.message_id and grouped it by userId.
Found plenty of duplicated message_id's and even though the value in the size field vary slightly with some everything else in the rows match. So are the duplicate? I believe they are.
In the event that they are I put together the below t-sql to build a table of the arc.id's and then planned to use a delete messages procedure I found in another post. I'm hoping someone with more familiarity of the data will verify that this will work.
Thanks
------------- BEGIN SQL ----------------
--CREATE TABLE TO PUT DUPLICATES FOUND IN
create table Duplicates (msg_count int, userName varchar(255), userId int, message_id varchar(255))
-- Insert into duplicate table by selecting message_id's grouped by user/owner and have a count > 1
insert into Duplicates (msg_count, userName, userId, message_id)
select count(arc.message_id) as msg_count, arc_users.userName, arc_owners.usrId, arc.message_id
from arc_owners INNER JOIN arc ON arc_owners.id = arc.id
INNER JOIN arc_users ON arc_owners.usrId = arc_users.usrId
group by arc_users.userName, arc.message_id, arc_owners.usrId
HAVING COUNT(arc.message_id) > 1
order by userName, msg_count desc
-- Create and open up a cursor to loop through the users
declare userCursor scroll cursor for
select distinct userId from Duplicates order by userId
open userCursor
declare @userId int
fetch first from userCursor into @userId
-- begin loop through unique users in duplicates table
while @@fetch_status = 0
begin
-- create and open a cursor to pull count of messages and message_id from duplicates tbl
declare userMessages scroll cursor for
select msg_count, message_id from Duplicates where userId = @userId
declare @msg_count int, @message_id varchar(255)
fetch first from userMessages into @msg_count, @message_id
-- begin looping through message_id's
while @@fetch_status = 0
begin
-- subtract 1 from the amount of duplicate message_id count
-- want to be sure to keep one of the messages
declare @topNum int
set @topNum = @msg_count - 1
declare @sql nvarchar(1000)
set @sql = ''
-- build sql string to select the @topNum amount of arc.id's from arc table and insert them into a PurgeTable
set @sql = 'insert into PurgeTable select top ' + convert(nvarchar, @topNum) + ' arc.id '
set @sql = @sql + 'from arc_owners INNER JOIN arc ON arc_owners.id = arc.id INNER JOIN arc_users ON arc_owners.usrId = arc_users.usrId '
set @sql = @sql + 'where arc_owners.usrId = ' + convert(nvarchar, @userId) + ' and message_id = ''' + @message_id & ''''
exec sp_executesql @sql
-- get next message_id
fetch next from userMessages into @msg_count, @message_id
end
-- clean up message cursor
close userMessages
deallocate userMessages
-- fetch next user in duplicates table
fetch next from userCursor into @userId
end
-- clean up user cursor
close userCursor
deallocate userCursor
drop table Duplicates
|
Printable Version
Duplicate emails on PST import. Verify SQL? - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread