MERGE in Sql Server

From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done. Syntax Create two tables and Insert some records Then do Merge Now view TargetTable Data  

Custom Model Binding in MVC

Some times we have to use our own Modal binding mechanism in MVC. In MVC default Modal binding machansim is perfect. It binds our modal to posted variables . We can create our custom Modal binder. We can use DefaultModalBinder Or IModalBinder to do that. In following video you can find Custom ModalBinder Using IModalBinder. Custom Modal Binder in MVC Download Source Code

format currency in jquery

So when we are in web development we want show Currency value with some formatting. Like thousand separator, decimal separator and currency symbol. I am using below Jquery function to format the currency values. We can format currency value as we required. If we just pass the currency value it will give us value with default formatting. formatCurrency(5873839) will give us ” $5,873,839.00″

unable to open bcp host data-file

So when I am using bcp utility in sql server I got this error. All syntax are fine. I was wandering. Issue was sql agent’s user not having permission to access host file location. So first I wanted to know sql agent’s current user. I used following query to sql server service acounts. Then grant permission to sql agent user. Everything works fine.

PIVOT in SQL SERVER

One of the more complex statements that we can execute inside sql server is a PIVOT statement. What it does?  PIVOT statement allows us to convert what would normally be row data into column data. Sounds Crazy… In simply let’s imagine we have a table as follow. In above table years are in rows. Let’s suppose we want to get data by years. Now it’s time to use PIVOT. Using PIVOT we can convert years into columns. PIVOT syntax SELECT <NonPivot> , <FirstPivotedColumn> , … FROM <Table containing data> PIVOT (FUNCTION(<data column>) FOR <List of pivoted columns>) AS <alias> Let’s do SELECT * FROM @Table PIVOT (SUM(Sales) FOR [Year] IN([2010],[2012]) )AS pvt The we get result