Delete duplicated rows on SQL

Assuming these are the incriminated records

duplicate_records

 

 

 

 

 

 

 

 

 

Let’s narrow down the duplicate ones by grouping the records and removing from the resultset the first valid record and by leaving the duplicates only.

narrow_records_duplicate

 

 

 

 

 

 

 

Let’s delete them now.

delete_records_duplicate

 

 

SQL Collation conflict

While attempting to copy  records from older database backup

update a
set latitude = b.latitude, longitude = b.Longitude
from newtable a
inner join [OldDb].[dbo].[LegacyTable] b on
b.name = a.name

I’ve encoutered the following :

Msg 468, Level 16, State 9, Line 5
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Possibly caused because the file comes from SQL Server 2008 while now I’m on the 2012. 

Luckly easy to fix by adding COLLATE DATABASE_DEFAULT on the columns used by the equals operator:


update a
set latitude = b.latitude, longitude = b.Longitude
from newtable a
inner join [OldDb].[dbo].[LegacyTable] b on
b.name = a.name
b.name COLLATE DATABASE_DEFAULT = a.name COLLATE DATABASE_DEFAULT

 

SQL – Remove chars from string

To be used when there’s the need or removing one or more chars from a record in a SQL table:

UPDATE TableName SET ColumnName = replace( ColumnName, '"', '' ); -- Remove commas

UPDATE TableName SET ColumnName = replace( replace(ColumnName, ',', ''), '"', '' );  -- Remove multiple chars: comma and quotes

 

Import data in SQL Express Management Studio

One of the restriction of the Sql Express Managent Studio is not allowing import/export of data. To overcome to this limitation we can either download the Sql Server Express Edition Toolkit or we can use the BULK INSERT statement in order to import a file.

This will be our document:

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt -- john
%
ut labore et dolore magna aliqua. Ut enim ad minim veniam -- mario
%
sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaera -- ugo
%
quis nostrum exercitationem ullam corporis suscipit laboriosam -- anne
%
vel illum qui dolorem eum fugiat quo voluptas nulla pariatur -- vera

We create first a temporary table and store the content of the file in it:

CREATE TABLE #TempTbl
(
    Phrase nvarchar(max)
)

BULK INSERT #TempTbl
FROM 'C:tempsampleCommaSep.csv'
WITH
(
    ROWTERMINATOR='%'
)

It is possible to specity the column delimiter of the csv specifing  FIELDTERMINATOR in the query:

CREATE TABLE #TempTbl
(
    Phrase nvarchar(max),
    Author nvarchar(150)
)

BULK INSERT #TempTbl
FROM 'C:tempsampleCommaSep.csv'
WITH
(
    FIELDTERMINATOR = '--',
    ROWTERMINATOR='%'
)