SQL Query to delete duplicate records

Hey Guys...
There was a small logical check missed in my automated blog(KuwaitFresh.com). And because of this there occured duplicate records which means duplicate posts! which will be not be accepted by any user :) Here is the query to delete duplicate records in your table...

List Duplicate Rows:

SELECT Field,Count(*)

FROM TABLE

GROUP BY FIELD

HAVING ( COUNT(*) > 1 )

DELETE duplicate Rows:

General Syntax:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.DUPLICATEFIELD = T2. DUPLICATEFIELD

and T1. UNIQUEFIELD > T2.UNIQUEFIELD)

Example:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.TITLE = T2.TITLE

and T1.POSTID > T2.POSTID)

Hope It is Helpful :-)

Regards

Fauzi ~ 4Z

SQL Query to List all tables with their Row counts in a Database

Hi there...

I was in need to analyze a database. This SQL Query to List all tables with their Row counts in a Database was pretty much useful.

SELECT

[TableName] = so.name,

[RowCount] = MAX(si.rows)

FROM

sysobjects so,

sysindexes si

WHERE

so.xtype = 'U'

AND

si.id = OBJECT_ID(so.name)

GROUP BY

so.name

ORDER BY

2 DESC

Hope it helpz
Fauzi

SQL: Query to find duplicate rows

Hi,

If you would like to list duplicate rows with the count for a table, the following query would be helpful.

Query: Duplicate Rows

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) > 1 )

And there are scenarios where we need to find the combination of Columns in a table occurred only once. In such case the following query could be used.

Query: Unique combination of columns

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) = 1 )

Hope it helps.

Regards
Fauzi

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Hi...

Came across this error today when i run a simple query in query analyzer...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

It was when i gave a test date with dd/mm/yyyy format. Seems we need to give in mm/dd/yyyy format... now it works fine....

people discussed on the same here

correct query is:

(SELECT
TEMP.Sre,
TEMP.Tket,
TEMP.RDate,
TEMP.SKU,
TEMP.Qty,
TEMP.Esion,
SAM.AverageCost*TEMP.Qty AS Expr2
FROM
TEMP INNER JOIN SAM ON (TEMP.SKU = SAM.SKU) AND (TEMP.Store = SAM.Sre)
WHERE
TEMP.RDate >= '07/13/2009'
And TEMP.RDate <= '07/19/2009'
) ORDER BY TEMP.Sre, TEMP.Tket

Regards
Fauzi

Sample to use XML in Stored Procedure

Hi,

Here is a sample to use XML string as a input to a Stored procedure. The stored procedure uses SP sp_xml_preparedocument. Which is capable of reading a XML string. It parses the XML and provides a parsed document which could be used like a temporary table in run time.

Following is the simple code:

1. SQL Script to Create Table:

/****** Object: Table [dbo].[Fund_Index_Relationship] Script Date: 05/24/2009 12:21:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Fund_Index_Relationship](

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

[Fund_ID] [int] NULL,

[FundIndex_ID] [int] NOT NULL,

CONSTRAINT [PK_Fund_Index_Relationship] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2. Stored Procedure:

-- =============================================

-- Author: Fauzi

-- Create date: 180808

-- Description: TO update Relationship between Fund & Index

-- =============================================

CREATE PROCEDURE [dbo].[XML_SP_Sample]

@xmlString TEXT,

@Fund_ID int,

@Output VARCHAR(1000) OUTPUT

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

-- Insert Section

INSERT into [Fund_Index_Relationship]

(

Fund_ID,

FundIndex_ID

)

SELECT

Fund_ID,

FundIndex_ID

FROM OPENXML (@idoc, '/Items/Item',1)

WITH (

Fund_ID int './Fund_ID',

FundIndex_ID int './FundIndex_ID'

)

END

3. XML string to Test SP:

[XML_SP_Sample] '<Items><Item><Fund_ID>34</Fund_ID><FundIndex_ID>2</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>3</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>4</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>5</FundIndex_ID></Item></Items>'

Hope it Helps :)

Regards

Fauzi

NVARCHAR vs VARCHAR

When i goggled to find the difference between using NVARCHAR versus VARCHAR, found this neat & simple explanation on the same:

http://weblogs.asp.net/guys/archive/2005/01/15/353550.aspx

How to delete all records from all tables from Database

Dear Reader,

Following is the SQL query used to delete all records from all tables from Database.
This is useful during development scenarios where you need to get rid of all the dummy records and insert a fresh data.

SQL Query: Appropriate comments are provided to explain its operation

/* The Following set of queries is used to delete all records from all tables from your database */

/* Disables Referential integrity */

/*---------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

/* Delete records from tables */

/*----------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?

'

GO

/* Enables Referential integrity */

/*-------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

/*Query ot be used if you would like to reseed all table*/

/*------------------------------------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)

'

GO

Hope its Helpful

Regards
Fauzi