Problem arises as the modified application has new indexes or change the table properties 'SaveDataPerCompany' to No instead of default Yes. Both may cause duplicate record problem. I don't want to just delete the entire record yet I need to synchronize the table with Axapta application.
It will be no problem if the table contains less than 20 records, I can just delete it manually. The problem is that I find a table has hundreds of records. Then I write this script to do this job less tedious.
--drop table #mytemptable -- This is needed only if run more than once
select distinct
module,type,groupid --specify as many as key fields
into #mytemptable
from pricediscgroup --this is the table name
--set variable s correspond to the key fields
declare @module int
declare @type intdeclare @groupid varchar(20)
declare @numberofrows int
declare mycursor Cursor for
SELECT * from #mytemptable
open mycursor
fetch next from mycursor into @module,@type,@groupid
while @@fetch_status=0
begin
select @numberofrows = count(*) from pricediscgroup where
module = @module and type= @type and groupid = @groupid
select @numberofrows, @module,@type,@groupid
if @numberofrows > 1
begin
set @numberofrows = @numberofrows - 1 -- leave one row
set rowcount @numberofrows --the select within the subquery will return this number of rows,
delete from pricediscgroup where recid in (
select recid from pricediscgroup p2
where
p2.module = @module and p2.type= @type and p2.groupid = @groupid)
end
fetch next from mycursor into @module,@type,@groupid
end
close mycursor
deallocate mycursor
It does take time to change the table name and adding some variables depend on the duplicate fields found. But it takes less time rather than deleting many rows manually.
