SQL TRACKER: 2011

Monday, November 14, 2011

DELETE A DUPLICATE RECORD IN TABLE USING RANK AND WITH

DELETE A DUPLICATE RECORD IN TABLE USING RANK AND WITH…

I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.

This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.

/* Create Table with 7 entries - 3 are duplicate entries */

CREATE TABLE DUPLICATE (Col1 INT, Col2 INT)

INSERT INTO DUPLICATE

SELECT 1,1

UNION ALL

SELECT 1,1

UNION ALL

SELECT 1,1

UNION ALL

SELECT 1,2

UNION ALL

SELECT 1,2

UNION ALL

SELECT 1,3

UNION ALL

SELECT 1,4

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

SELECT * FROM DUPLICATE

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

- - DELETE DUPLICATE RECORDS

WITH TEN (COL1,COL2.DUPL )

AS (SELECT COL1,COL2,

ROW_NOUMBER() OVER (PARTITION BY COL1,COL2 ODER BY COL1) AS DUPL

FROM DUPLICATE

)

DELETE

FROM TEN WHERE DUPL>1

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

SELECT * FROM DUPLICATE

--
--

Regards,

Basheer Ahamed

Monday, October 24, 2011

Pivot and Unpivot

-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT * FROM Product
GO-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO

-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

Thursday, October 20, 2011

Drop Constraint in a table

Drop Constraint in a table

ALTER TABLE [tblexternalload]

DROP CONSTRAINT [DF_tblexternalload_last_modifydate]

--
--

Regards,

Basheer Ahamed

Wednesday, October 19, 2011

Read XML Dicom Tag...

Read XML Dicom Tag..Using Sql Server..

<dicom>

<attr tag="00400252" vr="CS" len="10">COMPLETED</attr>

<item>

</item>

</attr>

<item>

<attr tag="00181030" vr="LO" len="8">Regular</attr>

</item>

</attr>

</dicom>

SQL QUERY:

DECLARE @doc xml

Declare @id int

declare @idoc int

Declare @patienid as nvarchar(100)

select @doc=DATA from TBLLOADDEAFULT

select @doc

EXEC

sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT

tag,len,comment

FROM

OPENXML(@idoc, '/dicom/attr')

WITH

(tag varchar(30)

,len varchar(30)

,comment ntext 'text()'

)

--
--

Regards,

Basheer Ahamed

Friday, October 14, 2011

Transfer column to row with coma separator

Transfer column to row with coma separator

Declare @finalmsg as varchar(max)

SELECT

@finalmsg=

STUFF((

SELECT ', ' + cast(centerid as varchar(max)) from MedallWarehouse.dbo.TblCorpCenterMaster where Obsolute=0

FOR XML PATH('') ), 1, 1, '')

select @finalmsg

(column name)

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 58, 59, 60, 61, 62

Thursday, September 15, 2011

TRIGGER WITH EX:

If I Inset Any Value In the Table it will Be Automatically Push to Another Table ,So Use This bellow steps…

CREATE TABLE [dbo].[Employee_Test](

[Emp_ID] [int] IDENTITY(1,1) NOT NULL,

[Emp_name] [varchar](100) NULL,

[Emp_Sal] [decimal](10, 2) NULL

) ON [PRIMARY]

INSERT A TABLE VALUE

INSERT INTO Employee_Test VALUES ('Anees',1000);

INSERT INTO Employee_Test VALUES ('Rick',1200);

INSERT INTO Employee_Test VALUES ('John',1100);

INSERT INTO Employee_Test VALUES ('Stephen',1300);

INSERT INTO Employee_Test VALUES ('Maria',1400);

Which Table U Want to Insert the content so Create a Table..

CREATE TABLE [dbo].[Employee_Test_Audit](

[Emp_ID] [int] NULL,

[Emp_name] [varchar](100) NULL,

[Emp_Sal] [decimal](10, 2) NULL,

[Audit_Action] [varchar](100) NULL,

[Audit_Timestamp] [datetime] NULL

) ON [PRIMARY]

This trigger is fired after an INSERT on the table. Let's create the trigger as:-

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let's create the trigger as:-

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)

set @audit_action='Updated Record -- After Update Trigger.';

if update(Emp_Sal)

set @audit_action='Updated Record -- After Update Trigger.';

insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'

UPDATE THE TABLE:

update Employee_Test set Emp_Sal=1550 where Emp_ID=6

THEN SELECT THE OUR REFERANCE TABLE:

select * from Employee_Test_Audit

AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let's create the trigger as:-

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]

AFTER DELETE

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @audit_action varchar(100);

select @empid=d.Emp_ID from deleted d;

select @empname=d.Emp_Name from deleted d;

select @empsal=d.Emp_Sal from deleted d;

set @audit_action='Deleted -- After Delete Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER DELETE TRIGGER fired.'

DELETE THE SELECTED ROW

delete from [Employee_Test] where [Emp_ID]=1

select * from Employee_Test_Audit

--
--

Regards,

Basheer Ahamed