Posts Tagged: sql

  • Development

    Change owner schema for all database tables

    Posted on by

    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.

  • Development

    Delete duplicated rows on SQL

    Posted on by

    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.  

  • Development

    SQL Collation conflict

    Posted on by

    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… Read more »

  • Development

    SQL- Update one table from another with inner join

    Posted on by

    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

  • Development

    SQL – Remove chars from string

    Posted on by

    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

  • Development

    EF Code first with MVC 3 scaffholding.

    Posted on by

    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.

  • Development

    Trim colums in Sql

    Posted on by

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

  • Development

    Import data in SQL Express Management Studio

    Posted on by

    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,… Read more »

  • Development

    Update da una select

    Posted on by

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