Tagged with sql

Delete duplicated rows on SQL

Assuming these are the incriminated 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.               Let’s delete them now.  

SQL Collation conflict

While attempting to copy ¬†records from older database backup 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 … Continue reading

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

EF Code first with MVC 3 scaffholding.

Here’s a sample of Code first and the MVC 3 scaffholding in action, in order to run it you’ll need the MVC 3 Tools Update extension and the Nuget package for MvcScaffholding. The schema is pretty simple: Dowload the source code.

Trim colums in Sql

Remove white spaces from the beginning and the end of a column : UPDATE TableName SET columnOne = LTRIM(RTRIM(columnOne)), SET columnTwo = LTRIM(RTRIM(columnTwo))

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, … Continue reading

Update da una select

1: UPDATE Table1 SET Field1 = NULL FROM (SELECT TOP 10 FROM Table1 ORDER BY Created DESC) Table1