Saturday, December 4, 2010

SQL Server 2008 - MERGE statement

SQL server 2008 has introduced a new feature to merge two tables. It lets you write a single SQL statement to insert, update and delete the records in the target table based on some conditions. It is very useful where you need to sync two tables. It avoids writing separate statements and logic for insert, update and delete.

It's syntax is very simple and straight forward:

MERGE <target_table> [AS table_alias]

USING <table_source> [AS table_alias]

ON <search_condition>

[WHEN MATCHED [AND clause_search_condition]

THEN <merge_matched> ]

[WHEN NOT MATCHED [BY TARGET] [AND clause_search_condition]

THEN <merge_not_matched> ]

[WHEN NOT MATCHED BY SOURCE [AND clause_search_condition]

THEN <merge_ matched> ];

Let me write an example and explain how MERGE works.

Let's say we have two tables named Books_Source and Books_Target which need to be synced periodically. And at one instance

The table Books_Source contains:

BookID BookName Quantity

1 "Learn ASP.Net" 3

3 "SQL tips" 5

4 "Complete Reference of JAVA" 2


The table Books_Target contains:

BookID BookName Quantity

1 "Learn ASP.Net" 2

2 "Learn XML" 4

4 "Complete Reference of JAVA" 4

To merge the above tables our MERGE statement goes like this:

MERGE Books_Target AS t

USING Books_Source AS s

ON t.BookID = s.BookID

WHEN MATCHED AND s.Quantity != t.Qunatity

THEN UPDATE SET t.Quantity = s.Quantity

WHEN NOT MATCHED BY TARGET

THEN INSERT(BookID, BookName, Quantity) VALUES(s.BookID, s.BookName, s.Quantity)

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

  • As you can see in the above code, insert, update and delete actions happen in a single MERGE statement. It lets us add our own conditions to merge conflicts and our own merge actions to take place.
  • In this example we have written update statement where the records exist in both tables but the column Quantity is different. In this case I want to update only when quantity gets changed in Order_Source table.
  • Similarly we have written insert statement where the records exist in source table but not in target table. Here we have not added any additional condition because we wanted to insert all those don't exist in target table.
  • In the same way we have written DELETE statement where the records exist in target table but not in the source table. In this case we don't want the records those are not in source table.
  • MERGE statement is more efficient than separate statements for insert, update and delete operation to merge tables

The final result after the execution of above statement is:

BookID BookName Quantity
1 "Learn ASP.Net" 3
3 "SQL tips" 5
4 "Complete Reference of JAVA" 2

Wednesday, December 1, 2010

Jquery language translation plugin

Many of us know that Google is providing an online tool to translate from one language to another language. Of course this translation may not be accurate but the percentage of accuracy varies from language to language based on the language structure. Basically it translates word by word. This tool is really helpful to understand statements in different languages.

But if we have a our own website which is completely in English and there are users which don't know English and they want to know about our site then it is a problem. In this case they may not know that there is a tool which translates text in English to their native language or they don't have time to translate each and every page or they may not be interested to translate at all. Then they may simply skip reading and ignore your site.

To avoid these kind of risks Google also provided us translator API which lets us develop our website to translate from one language to another language. To know more about Google translator please go through the link http://code.google.com/apis/language/translate/v1/getting_started.html. It is very easy to integrate with our website by following this link.

But the major problem with the Google translate API is its limitation with the length of the text we pass to translator. It does not translate if we pass text with more than 5000 characters. It is only helpful where we need to translate a portion of our web page and that too with less than 5000 characters. If we have to translate the complete web page then we need to write complex logic to split the html code into 5000 character sets and translate thru API and then merge the results. This is really paining.

To solve these problems we have Jquery plugin for translator. You can download this at http://jquery-translate.googlecode.com/files/jquery.translate-1.3.9.min.js. It internally uses Google translator API and applies the above discussed logic and gives us the complete translated html. This takes the complex part and leave us a simplest part to translate complete web page.

The below code will do complete translation of your web page from source language to destination language

$('body').translate( srcLang, destLang );

Eg: $('body').translate( "en", "es"); //translates text from English to Spanish

But to get it working you also need to make sure you have Jquery along with this plugin.

You can also try playing with this plugin and see how it works. Please take a look at http://jsbin.com/emufo/edit and make any changes you want and preview the behavior by clicking Preview link on top left corner.

kick it on DotNetKicks.com