IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSOBJECTS.name = 'HYATIS_DUPLIKESI_OLANLAR_TMP')
SELECT *
INTO [HYATIS_DUPLIKESI_OLANLAR_TMP]
FROM HYATIS
WHERE HESAPISLID IN(
	SELECT HESAPISLID
	FROM HYATIS 
	GROUP BY HESAPISLID
	HAVING COUNT(*) > 1
)
GO

DELETE FROM HYATIS
WHERE HESAPISLID IN(
    SELECT HESAPISLID
    FROM HYATIS 
    GROUP BY HESAPISLID
    HAVING COUNT(*) > 1
)
AND 
HYATISID NOT IN(
    SELECT MAX(HYATISID)
    FROM HYATIS 
    GROUP BY HESAPISLID
    HAVING COUNT(*) > 1
)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HYATIS]') AND name = N'IX_HYATIS_HESAPISLID')
CREATE UNIQUE INDEX IX_HYATIS_HESAPISLID ON HYATIS([HESAPISLID]) INCLUDE(HYATISID,DURUM,HYYATISTIPI,OSPROTOKOLNO)
WITH(DROP_EXISTING=OFF,FILLFACTOR=95)
GO

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HYATIS]') AND name = N'IX_HYATIS_HESAPISLID_HYATISID_DURUM')
DROP INDEX [IX_HYATIS_HESAPISLID_HYATISID_DURUM] ON [dbo].[HYATIS]
GO

