Finding Duplicates SQL

by Nathan 25. July 2011 07:20

There are various ways to find duplicates in sql server.

 

Here are some example SQL Codes for it.

 

select count(*),description, min(entrytime) as firstbet ,max(entrytime) as lastrecord,
datediff(s,min(entrytime),max(entrytime)) as secondsapart
from mytable with(nolock)
where 1=1
group by description
having count(*) > 2

if more than one record you will need to add more columns to the group by statement

select count(*),firstname,lastname, min(datecreated) as firstbet ,
max(datecreated) as lastrecord,
datediff(s,min(datecreated),max(datecreated)) as secondsapart
from mytable with(nolock)
where 1=1
and datecreated > GETDATE()-130
group by firstname,lastname
having count(*) > 2

 

the next step is to remove duplicates here is how it can be done...

 

 

Tags:

SQL

Shrinking Log File SQL 2005 / 2008

by Nathan 17. March 2011 07:55

Here is the code to shrink a bloated log file:

 

SQL 2005

DBCC SHRINKFILE('<LogFileName>', 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE('<LogFileName>', 1)

SQL 2008

USE <databaseName>
GO

ALTER DATABASE <databaseName> Set Recovery Simple
GO

ALTER DATABASE <databaseName> Set Recovery Full
GO

DBCC SHRINKFILE ('<LogFileName>', 1)
GO


FINDING NAME OF SQL LOG AND DATABASE FILE

To find the logfile and database names you can execute the below procedure

use <databaseName>

exec sp_helpfile

Tags:

SQL

Search Stored Procedure for Text

by Nathan 17. November 2010 08:37

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%keyword%'
    AND ROUTINE_TYPE='PROCEDURE'

Tags: , ,

SQL

Adding Query Headers to SQL Management Studio

by Nathan 23. January 2010 18:00

adding column headers to query analyser 2000 / sql server management studio 2005

tools > options > query results > sql server > results to grid

Tags:

SQL

SQL UPDATE INNER JOIN

by Nathan 13. July 2009 06:41

 

If you want to update the data in a table by getting information from another table.  You can acheive this by using an update inner join query.

For example if i wanted to update the phone numbers in my table using a list of contacts from a secondary table, the UPDATE INNER JOIN Query would be like the one below:

UPDATE c
SET c.PhoneNumber = c2.PhoneNumber
FROM Contact AS c
INNER JOIN ContactTable2 c2 on c.Id = c2.Id
WHERE DateCreated > GETDATE()-60

 

Microsoft SQL Server

Tags:

SQL

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

by Nathan 29. March 2009 08:29

I have come across this error when using a Indentity In a stored procedure.

In a stored procedure you need to define the scope indentity in a variable before you can use it in a select statement

So instead of

SELECT * FROM table WHERE id = SCOPE_IDENTITY()

You need to use 

 

SET @var = SCOPE_IDENTITY();

SELECT * FROM table WHERE id = @var

 

 @@IDENTITY  will return the last indentity across the whole database whilst SCOPE_IDENTITY() will return only the indentity related to the scope of the operation

Tags:

SQL

Get Column Names for Table in MSSQL

by Nathan 3. February 2009 17:26

To get the column names of a table in MSSQL 

SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

This is basically the MSSQL equivalent for The MySQL Command DESCRIBE TableName which displays the table details

 

I often use this so i can copy and paste into another query etc.

SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

then i have the commas for use in any INSERT or SELECT etc Smile

 

Tags:

SQL

Alternative to using SQL Cursors

by Nathan 13. January 2009 13:09

 

USING A CUSROR

DECLARE @FirstName varchar(255), @LastName varchar(255)

DECLARE user_cursor CURSOR FOR

SELECT FirstName,LastName FROM Users

OPEN user_cursor

FETCH NEXT FROM user_cursor

INTO  @FirstName, @LastName

WHILE @@FETCH_STATUS = 0

BEGIN

print @FirstName

FETCH NEXT FROM user_cursor INTO  @FirstName, @LastName

END

CLOSE user_cursor

DEALLOCATE user_cursor

 

 

USING A TEMPORARY TABLE/VARIABLE

SET NOCOUNT ON
DECLARE @Users TABLE (
 RowID int IDENTITY(1, 1),
 FirstName varchar(255),
 LastName varchar(255)
)

DECLARE @NumberOfRecords int, @RowCount int
DECLARE @FirstName varchar(255)

INSERT INTO @Users(FirstName,LastName)
SELECT FirstName,LastName FROM Users

SET @NumberOfRecords = @@ROWCOUNT
SET @RowCount = 1


WHILE @RowCount <= @NumberOfRecords
BEGIN        
  -- Do Operations
SET @RowCount = @RowCount + 1
END

 

Tags:

SQL

Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen