sql (9)

Change owner schema for all database tables

Had this issue when restoring an Umbraco database created under a different user. This will alter the schema owner on all tables of the target database.

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 […]

SQL- Update one table from another with inner join

To use when there’s the need to update a table with values from another table using a column match (join). UPDATE TABLE1 SET Colum1 = T2.Column1 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.MatchID = T2.MatchID

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, […]

Update da una select

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