Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records could be done by several ways so this is another way to do the same.
Well, you can use the same table structure and record sets given in my previous article on the nearly same topic.
https://riteshshah.wordpress.com/2009/02/28/select-duplicate-records-in-ms-sql-server/
OR
http://ritesh-a-shah.blogspot.com/2009/02/sometime-we-require-finding-duplicate.html

Once you create table given in above link, you can use the below given query for selecting or deleting records.

WITH DeleteDuplicateRecords AS

(

select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum

from SelectDuplicate

)

Select * from DeleteDuplicateRecords where RowNum>1

Or

WITH DeleteDuplicateRecords AS

(

select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum

from SelectDuplicate

)

DELETE from DeleteDuplicateRecords where RowNum>1

Reference: Ritesh Shah

CTE (Common Table Expression) SQL-Server 2005

One of the beautiful features of MS SQL-Server 2005 is CTE and it is mainly used for recursive query. In recursive query, query executes itself; it is the same concept we used to use in C or C# etc programming language for recursive function. In real world we often need recursive hierarchical data for list of category and subcategory resides in one single table. Let’s have a look at it.

Step 1:

Simply create one table.

USE AdventureWorks

GO

Create Table VehicleCategory

(

ID Int Constraint PK_VehicleCategoryID Primary Key,

BikeCategory VarChar(100),

ParentID Int Constraint FK_VehicleCategory_ParentID References VehicleCategory(ID),

Description varchar(50)

)

GO

Step 2:

Insert few records in above created table:

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(1,‘Bike’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(2,‘Scooter’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(3,‘Yamaha RX 100’,1,‘125 CC bike of Yamaha’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(4,‘Hero Honda CBZ’,1,‘150 CC bike of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(5,‘Honda Activa’,2,‘125 CC Scooter of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(6,‘TVS Scooty’,2,’75 CC Scooter of TVS’)

Step 3:

Now, if I want all the category and subcategory of “Bike” than what I suppose to do? There may be different ways to fulfill this requirement but I would like to go for new feature introduce in SQL-Server 2005, which is CTE (Common Table Expression)

WITH cte_VehicleCategory AS

(

SELECT ID,BikeCategory,ParentID,Description FROM VehicleCategory Where ID=1

UNION ALL

SELECT v.ID,v.BikeCategory,v.ParentID,v.Description FROM VehicleCategory AS v

INNER JOIN

cte_VehicleCategory AS cv ON v.ParentID=cv.ID

)

Select * from cte_VehicleCategory

Let’s study little bit more about how this magical code worked???

Our CTE name is cte_VehicleCategory, which will get its base records from the first query above the UNION ALL. It will iterate to seek ID’s value in parentid of query below the UNION ALL. Now, as you know that this is recursive query so if you want to create infinite query 😉 than change statement after “INNER JOIN” to “cte_VehicleCategory AS cv ON v.ID=cv.ID”

Reference: Ritesh Shah

Select-Delete-Duplicate-Records-SQL-Server

Sometime we require finding duplicate records in our table. There are several ways to it and I will keep posting separate article for all ways to do the same task. Here is one very short and sweet solution by simple SQL query. Have a look at it.

Well, I am going to create one simple table in AdventureWorks database.

use adventureworks

GO

Create Table SelectDuplicate

(

ID int identity(1,1) not null,

Fname varchar(10) not null,

Lname varchar(10) not null,

City varchar(10) not null

)

Now, this is a time to enter some records in just created table above.

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘Ahmedabad’)

INSERT INTO SelectDuplicate VALUES(‘Avi’,‘Sagiv’,‘Edison’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Parsipenny’)

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘WestField’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Ahmedabad’)

So, here is the heart of article below.

SELECT * FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

As I mentioned in first paragraph of article as well, there are few different ways to do the same thing which will be explained in coming articles.

Of course, you can delete these duplicate records as well, if you wish. All you need to do is change first “SELECT *” to “DELETE” as below given code.

DELETE FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

Reference: Ritesh Shah

Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records could be done by several ways so this is another way to do the same.

You can refer that article by following link.
https://riteshshah.wordpress.com/2009/02/28/select-duplicate-records-in-ms-sql-server/
OR
http://ritesh-a-shah.blogspot.com/2009/02/sometime-we-require-finding-duplicate.html

 

As this is going to be done by CTE, You can get more information about CTE by following link.
https://riteshshah.wordpress.com/2009/02/28/cte-common-table-expression-sql-server-2005/
OR
http://ritesh-a-shah.blogspot.com/2009/02/cte-common-table-expression-sql-server.html

 

Well, you can use the same table structure and record sets given in my previous article on the nearly same topic.
https://riteshshah.wordpress.com/2009/02/28/select-duplicate-records-in-ms-sql-server/
OR
http://ritesh-a-shah.blogspot.com/2009/02/sometime-we-require-finding-duplicate.html

 

Once you create table given in above link, you can use the below given query for selecting or deleting records.

WITH DeleteDuplicateRecords AS

(

select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum

from SelectDuplicate

)

Select * from DeleteDuplicateRecords where RowNum>1

 

Or

WITH DeleteDuplicateRecords AS

(

select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum

from SelectDuplicate

)

DELETE from DeleteDuplicateRecords where RowNum>1

Reference: Ritesh Shah

CTE (Common Table Expression) SQL-Server 2005

One of the beautiful features of MS SQL-Server 2005 is CTE and it is mainly used for recursive query.  In recursive query, query executes itself; it is the same concept we used to use in C or C# etc programming language for recursive function.  In real world we often need recursive hierarchical data for list of category and subcategory resides in one single table. Let’s have a look at it.

Step 1:

Simply create one table.

USE AdventureWorks

GO

 

Create Table VehicleCategory

(

ID Int Constraint PK_VehicleCategoryID Primary Key,

BikeCategory VarChar(100),

ParentID Int Constraint FK_VehicleCategory_ParentID References VehicleCategory(ID),

Description varchar(50)

)

GO

 

Step 2:

Insert few records in above created table:

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(1,‘Bike’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(2,‘Scooter’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(3,‘Yamaha RX 100’,1,‘125 CC bike of Yamaha’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(4,‘Hero Honda CBZ’,1,‘150 CC bike of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(5,‘Honda Activa’,2,‘125 CC Scooter of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(6,‘TVS Scooty’,2,’75 CC Scooter of TVS’)

Step 3:

Now, if I want all the category and subcategory of “Bike” than what I suppose to do? There may be different ways to fulfill this requirement but I would like to go for new feature introduce in SQL-Server 2005, which is CTE (Common Table Expression)

WITH cte_VehicleCategory AS

(

      SELECT ID,BikeCategory,ParentID,Description FROM VehicleCategory Where ID=1

     

            UNION ALL

 

      SELECT v.ID,v.BikeCategory,v.ParentID,v.Description FROM VehicleCategory AS v

            INNER JOIN

            cte_VehicleCategory AS cv ON v.ParentID=cv.ID

)

Select * from cte_VehicleCategory

 

Let’s study little bit more about how this magical code worked???

Our CTE name is cte_VehicleCategory, which will get its base records from the first query above the UNION ALL. It will iterate to seek ID’s value  in parentid of query below the UNION ALL. Now, as you know that this is recursive query so if you want to create infinite query 😉 than change statement after “INNER JOIN”  to “cte_VehicleCategory AS cv ON v.ID=cv.ID”

 

Reference: Ritesh Shah

Select Duplicate Records in MS SQL-Server

Sometime we require finding duplicate records in our table.  There are several ways to it and I will keep posting separate article for all ways to do the same task. Here is one very short and sweet solution by simple SQL query. Have a look at it.

Well, I am going to create one simple table in AdventureWorks database.

use adventureworks

GO

 

Create Table SelectDuplicate

(

ID int identity(1,1) not null,

Fname varchar(10) not null,

Lname varchar(10) not null,

City varchar(10) not null

)

Now, this is a time to enter some records in just created table above.

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘Ahmedabad’)

INSERT INTO SelectDuplicate VALUES(‘Avi’,‘Sagiv’,‘Edison’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Parsipenny’)

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘WestField’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Ahmedabad’)

So, here is the heart of article below.

SELECT * FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

As I mentioned in first paragraph of article as well, there are few different ways to do the same thing which will be explained in coming articles.

Of course, you can delete these duplicate records as well, if you wish. All you need to do is change first “SELECT *” to “DELETE” as below given code.

DELETE FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

Reference: Ritesh Shah

Email script from C# with System.Net.Mail namespace

Sending an email from C# script is quite easy thing. Most popular two ways to send an email from C# scripts are as follows:
1.)
public string sendingMail(string strFrom, string strTo, string strSubject, string strMessage)
{
MailMessage myMessage = new MailMessage(new MailAddress(strFrom), new MailAddress(strTo));
myMessage.IsBodyHtml = true;
myMessage.Priority = MailPriority.Normal;
myMessage.Body = strMessage;
myMessage.Subject = strSubject;
SmtpClient client = new SmtpClient(“”, 25);
try
{
client.Send(myMessage);
}
catch(Exception ex)
{
return ex.ToString();
}
return “Message Has Been Sent”;
}

2.)
public string AuthenticatedMailSend(string mailTo, string Subject, string MessageBody)
{
System.Net.Mail.MailMessage email = new MailMessage(“TEST@test.com”, mailTo);
email.Subject = Subject;
email.IsBodyHtml = true;
email.Body = MessageBody;

System.Net.Mail.SmtpClient mailClient = new SmtpClient();
System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential(“”, “”);
mailClient.Host = “”;
mailClient.Port = 25;
mailClient.UseDefaultCredentials = false;
mailClient.Credentials = basicAuthenticationInfo;

try
{
mailClient.Send(email);
}
catch (Exception ex)
{
return ex.ToString();
}

return “1”;
}

You ccan use any of the above methods, some mail server only supports authenticated email send by script. In that case, go for 2nd method.

Don’t forget to reference “using System.Net.Mail;” namespace for sending an email.