Delete Duplicate Rows SQL Server

1. Add an Identity Column to the Table

alter table MASTER
add identity_col int identity(1,1);

2. Delete duplicate data using Below Query (In the GROUP BY CLAUSE you can specify multiple columns if the row is indetified using more than one field)

delete from MASTER where identity_col not in ( select Min(identity_col) from MASTER group by trnx_uid);

Advertisements

Stored Procedure to Insert Data SQL Server

CREATE TABLE [dbo].[MASTER](
[BANK_CODE] [varchar](30) NULL,
[TRNX_UID] [float] NULL,
[CARD_TYPE] [varchar](30) NULL,
[DIR_FLAG] [varchar](30) NULL,
[ACQ_CODE] [varchar](30) NULL,
[ACQ_DESC] [varchar](200) NULL,
[ISS_CODE] [varchar](30) NULL,
[ISS_DESC] [varchar](200) NULL,
[TRNX_STATUS] [varchar](30) NULL,
[TRNX_TYPE] [varchar](30) NULL,
[TERMINAL_ID] [varchar](200) NULL,
[CARD_NO] [varchar](100) NULL,
[CURRENCY_CODE] [varchar](30) NULL,
[AMOUNT_FC] [numeric](20, 6) NULL,
[AMOUNT_LC] [numeric](20, 2) NULL,
[CURRENCY_RATE] [numeric](10, 6) NULL,
[TRNX_DATE] [datetime2](7) NULL,
[TRNX_TIME] [datetime2](7) NULL,
[REFERENCE_NO] [varchar](100) NULL,
[TRNX_MESSAGE] [varchar](4000) NULL,
[ACQ_COUNTRY] [varchar](30) NULL,
[REMARKS] [varchar](500) NULL,
[TIME_STAMP] [datetime2](7) NULL,
[ID$] [varbinary](16) NULL,
[IDENTITY_COL] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

— ======================================================================
— Author: Shadab Mohammad
— Create date: 05th May 2015
— Description: Stored Procedure to Insert Data in Master Table
— =======================================================================
CREATE Procedure [dbo].[InsertMaster]
(
@BANK_CODE varchar(30),
@TRNX_UID float,
@CARD_TYPE varchar(30),
@DIR_FLAG varchar(30),
@ACQ_CODE varchar(30),
@ACQ_DESC varchar(200),
@ISS_CODE varchar(30),
@ISS_DESC varchar(200),
@TRNX_STATUS varchar(30),
@TRNX_TYPE varchar(30),
@TERMINAL_ID varchar(200),
@CARD_NO varchar(100),
@CURRENCY_CODE varchar(30),
@AMOUNT_FC numeric(20, 6),
@AMOUNT_LC numeric(20, 2),
@CURRENCY_RATE numeric(10, 6),
@TRNX_DATE datetime2(7),
@TRNX_TIME datetime2(7),
@REFERENCE_NO varchar(100),
@TRNX_MESSAGE varchar(4000),
@ACQ_COUNTRY varchar(30),
@REMARKS varchar(500),
@TIME_STAMP datetime2(7)
)
As
Begin
Insert into MASTER (BANK_CODE,TRNX_UID,CARD_TYPE,DIR_FLAG,ACQ_CODE,ACQ_DESC,ISS_CODE,ISS_DESC,TRNX_STATUS,TRNX_TYPE,TERMINAL_ID,CARD_NO,CURRENCY_CODE,AMOUNT_FC,AMOUNT_LC,CURRENCY_RATE,TRNX_DATE,TRNX_TIME,REFERENCE_NO,TRNX_MESSAGE,ACQ_COUNTRY,REMARKS,TIME_STAMP)
Values(@BANK_CODE,@TRNX_UID,@CARD_TYPE,@DIR_FLAG,@ACQ_CODE,@ACQ_DESC,@ISS_CODE,@ISS_DESC,@TRNX_STATUS,@TRNX_TYPE,@TERMINAL_ID,@CARD_NO,@CURRENCY_CODE,@AMOUNT_FC,@AMOUNT_LC,@CURRENCY_RATE,@TRNX_DATE,@TRNX_TIME,@REFERENCE_NO,@TRNX_MESSAGE,@ACQ_COUNTRY,@REMARKS,@TIME_STAMP)
End

GO

— Execute the Stored Procedure

USE [dbname]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[InsertMaster]
@BANK_CODE = NULL,
@TRNX_UID = NULL,
@CARD_TYPE = NULL,
@DIR_FLAG = NULL,
@ACQ_CODE = NULL,
@ACQ_DESC = NULL,
@ISS_CODE = NULL,
@ISS_DESC = NULL,
@TRNX_STATUS = NULL,
@TRNX_TYPE = NULL,
@TERMINAL_ID = NULL,
@CARD_NO = NULL,
@CURRENCY_CODE = NULL,
@AMOUNT_FC = NULL,
@AMOUNT_LC = NULL,
@CURRENCY_RATE = NULL,
@TRNX_DATE = NULL,
@TRNX_TIME = NULL,
@REFERENCE_NO = NULL,
@TRNX_MESSAGE = NULL,
@ACQ_COUNTRY = NULL,
@REMARKS = NULL,
@TIME_STAMP = NULL

SELECT ‘Return Value’ = @return_value

GO