Delete duplicate records from table (SQL)


How to delete duplicate records from sql table

1) Scenario 1 : If there is no any identity column available in table then use Common table expression

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
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

GO

— See the result what it gives
WITH CTE (COL1, COL2, DUPLICATECOUNT)
AS
(
SELECT COL1, COL2, ROW_NUMBER() OVER( PARTITION BY COL1, COL2 ORDER BY COL1) AS DUPLICATECOUNT1
FROM DUPLICATERCORDTABLE
)
SELECT * FROM CTE  WHERE DUPLICATECOUNT > 1

— Delete the record

WITH CTE (COL1, COL2, DUPLICATEID )
AS
(
SELECT COL1, COL2, ROW_NUMBER() OVER(PARTITION BY COL1, COL2 ORDER BY COL1) AS DUPLICATEID
FROM DUPLICATERCORDTABLE
)
DELETE FROM CTE WHERE DUPLICATEID >1

2) Scenarion 2 :  if table contains the identity column

CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
GO
INSERT INTO TestTable (ID, NameCol)
SELECT 1, ‘First’
UNION ALL
SELECT 2, ‘Second’
UNION ALL
SELECT 3, ‘Second’
UNION ALL
SELECT 4, ‘Second’
UNION ALL
SELECT 5, ‘Second’
UNION ALL
SELECT 6, ‘Third’
GO

— Detecting Duplicate
SELECT NameCol, COUNT(*) TotalCount
FROM TestTable
GROUP BY NameCol
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
GO
— Deleting Duplicate
DELETE
FROM TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol
)
GO
Enjoy programming🙂

 

Dynamic Query in SQL


Hi All,
We can use dynamic query with two ways :
Note : We must have to declare variable which will hold the dynamic query as NVarchchar/Ntext/NChar

1) First :
Declare @SQL nVarChar(1000)  — N.B. string must be unicode for sp_executesql
SELECT @SQL = ‘SELECT * FROM tAction WHERE actionid =  @actionId and actionname=@actionName’

Exec sp_executesql @SQL, N’@actionId nVarChar(50), @actionName nvarchar(10)’, @actionId = 2, @actionName=’update’

2) Second:
Declare @SQLtext NVarChar(1000) — N.B. string must be unicode for sp_executesql
declare @actionId int
set @actionId = 2
set @SQLtext = ‘SELECT * FROM tAction WHERE actionid = ‘ + cast( @actionId as varchar(10))
exec sp_executesql @SQLtext

Keep Programming🙂

Differences between Stored Procedures and Functions


  • Procedure can return zero or n values whereas function can return one value which is mandatory.
  • Procedures can have input/output parameters for it whereas functions can have only input parameters.
  • Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
  • Functions can be called from procedure whereas procedures cannot be called from function.
  • Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
  • We can go for transaction management in procedure whereas we can’t go in function.
  • Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
  • UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
  • UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  • Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

In depth

Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have three main categories:

  1. Scalar-valued function – returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
  2. Inline function – can contain a single SELECT statement.
  3. Table-valued function – can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can’t enclose the logic for getting this rowset in a single SELECT statement.

reference : http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function

How to Change Style from Code behind in ASP.Net


Suppose you have a div in Asp.net page
like the given code
<div runat=”serverid=”divErrorstyle=”color: Red; font-weight: bold;” />
and you have to change div font color from red to green from code behind

Code

divError.Style[“color”] = “Green”;

OR

divError.Attributes.Add(“Style”, “color:Green; font-weight: bold;”);
Continue reading

Set default version for visual studio


Set default version for visual studio, when you have more than one version installed on you system and you want to set default one when you run from run command

Here is the solution HKEY_LOCAL_MACHINE\Software\Microsft\Windows\CurrentVersion\App Paths\devenv.exe Change the default path here Set the path like this

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe – VS-2008
                                                         Or
C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe – VS-2010

Arvind Kumar
[9910310129]