SQL SERVER – 2008 – Introduction to Merge Statement

 SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_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> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
Example:

Let’s create Student Details and StudentTotalMarks and inserted some records.

Student Details:

USE AdventureWorks
GOCREATE TABLE StudentDetails(StudentID INTEGER PRIMARY KEY,StudentName VARCHAR(15)
)GOINSERT INTO StudentDetailsVALUES(1,'SMITH')INSERT INTO StudentDetailsVALUES(2,'ALLEN')INSERT INTO StudentDetailsVALUES(3,'JONES')INSERT INTO StudentDetailsVALUES(4,'MARTIN')INSERT INTO StudentDetailsVALUES(5,'JAMES')GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks(StudentID INTEGER REFERENCES StudentDetails,StudentMarks INTEGER)GOINSERT INTO StudentTotalMarksVALUES(1,230)INSERT INTO StudentTotalMarksVALUES(2,255)INSERT INTO StudentTotalMarksVALUES(3,200)GO

In our example we will consider three main conditions while we merge this two tables.

1. Delete the records whose marks are more than 250.
2. Update marks and add 25 to each as internals if records exist.
3. Insert the records if record does not exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sdON stm.StudentID = sd.StudentIDWHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)VALUES(sd.StudentID,25);GO

There are two very important points to remember while using MERGE statement.

• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.

AS we can see there are 5 rows updated. StudentID 2 is deleted as it is more than 250, 25 marks have been added to all records that exists i.e StudentID 1,3 and the records that did not exists i.e. 4 and 5 are now inserted in StudentTotalMarks .

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.

abstract Class

Q.1 what is abstract Class? Ans. Abstract class means that, no object of this class can be instantiated, but can make derivations of this. Eg: abstract class absClass { }

PIVOT in Sql server 2005

With the new PIVOT operatorin SQL Server 2005, the CASE statement and GROUP BY statements are no longernecessary. (Every PIVOT query involves an aggregation of some type, so you canomit the GROUP BY statement.) The PIVOT operator provides the samefunctionality that we tried to achieve with the CASE statement query, but youcan achieve it through less code, and it is a bit more pleasing on your eyes. Listing C is a sample PIVOT query tomimic our CASE statement query.

The meat of this query iscontained in the parentheses after the PIVOT operator. Inside the parentheses,we are using a SUM aggregate function to sum up the SalePrice for each Productlisted in the FOR Product IN() statement. This statement may seem somewhat oddbecause the values in the IN() statement are not enclosed in tick (') marks tosignify that they are literal values. These values are actually treated asColumnNames, and they are transformed into tabular fields in the final resultset.

If you run this query, youwill get a result similar to the one from our CASE statement query. The bigdifference is that this query still returns one row for each row listed in ourtable, which is not desirable; we want a distinct list of rows for each year ofsales listed in our table. The problem lies in the way we first constructed ourPIVOT query.

In the SELECT statement inthe query, we simply listed the year and a field for each product listed in ourSalesHistory table. The problem is that the SELECT statement that precedes thePIVOT clauses cannot specify which columns to use in the PIVOT clause. The PIVOT clause applies the grouping to all columns that are not the pivoted or aggregated fields. We can use a subquery to achieve the desired results. Listing D features the resulting query. This small change allows usto mimic our CASE statement query with less code.
Limitations

The PIVOT operator is usefulin many situations, yet there are drawbacks. Much of the functionality providedby the PIVOT operator requires you to "hard code" the fields that youare pivoting into columnar data. You can overcome this by building the PIVOTquery through dynamic TSQL statements, but this is not the most desirablesolution.

Another potential drawback ofusing the PIVOT clause is that you can only aggregrate one field in the resultset; in the CASE statement queries, you can aggregate as many fields as needed.

While these potentialsetbacks may seem like a big deterrent from using the new PIVOT operator, itstill has its place in the TSQL developers' toolbelt.


Example:

First we see a simple output from simple Select Statement


PIVOT



Then We are going to Pivot this table

PIVOT

Nullable Types In C sharp

Why Nullable Types?

Value types are "primitive" data types such as numbers. The .NET Framework stores value types in the stack and implicitly initializes them to their default values—even if you do not explicitly initialize them when they are defined. For example, an int gets initialized to 0, a bool to false, and so on. Unfortunately, most value types are unable to represent a null value, which presents a problem when you're working with data-centric applications where null values are possible, because you have to select some other value to represent null. But whatever value you choose to represent null is no longer available as a valid data value, which restricts the range of permissible values. In other words, if you elect to use -1 to represent null, you've removed -1 as a valid value from the range of numbers that the value type can support. In addition, you must check to ensure that the chosen "null-representation" values are ignored in other parts of the application—and you must make sure not to display the null-representation values directly to end users, who may not understand that the value represents null.

Developers have tried various approaches to solve the null-value type mismatch, but each has its associated pitfalls. This is where nullable types can help. They're perfect for situations where you may need to represent a value type in an undefined state.

The most common such situation occurs when you want to assign the value from a column of a database table (that allows for null values) to a C# type.

The System.Nullable Structure
C# 2.0 provides a System.Nullable generic type struct that you can use to define nullable types. The constructor accepts one parameter—the type itself—and is defined as shown below:

namespace System
{
public struct Nullable : System.IComparable,
System.INullableValue
{
public Nullable(T value);
public static explicit operator T(T? value);
public static implicit operator T?(T value);
public T Value { get; }
public bool HasValue { get; }
public T GetValueOrDefault();
}
}
Here's the syntax you use to define a nullable type:

System.Nullable variable = null;
Note that the generic type T in the preceding code stands for a value type, not a reference type.

As a more concrete example, you can define a nullable integer type as follows:

System.Nullable patientBilledAmount;
The long version above has an equivalent shorthand version:

int? patientBilledAmount;
In C# 2.0, a question mark (?) suffix following a data type designator specifies that the type can accept null values.

If you refer back to the nullable struct definition shown earlier, notice that any nullable type contains two properties that you use in tandem to check for null values. If the type contains a non-null value, the HasValue property returns true; otherwise it returns false. On the other hand, the Value property returns a non-null value if and only if the HasValue property returns true; otherwise, it throws an exception. So typically, you check nullable type values as follows:

if (patientBilledAmount.HasValue)
Console.WriteLine(patientBilledAmount.Value);
else
Console.WriteLine("The amount is null");

Web Form Printing

Print a Web Page Using JavaScript

By Puneet Sharma

with updates by HTMLGoodies Editorial Staff

You've probably been on a web page and wanted to print out the page for later reference. Wouldn't it be great to be able to provide visitors to your site to be able to do just that? This tutorial for web developers will show you how to use JavaScript's window.print function, complete with examples!

The JavaScript window.print() Function

Print is a method of the object "window." At first I thought that the command was simply capturing the buttons along the top of the browser window so all I had to do was substitute "print" with one of the other items, like "mail" or "home". No dice. I also tried setting up the same type of format with items found in the menus like "properties" and "Options." Error after error. It's apparently put into the DTD as a method unto itself.

Saving Grace!

The good thing about the command, the way the authors set it up, is that it does not immediately fire a print. It actually only fires the print window, which still gives your site's visitors a choice in the matter!

It's then up to the person who triggered the method whether to then continue with the printing or not. That's good because I can see a ton of problems with printers not being turned on and huge, huge files being set to print.

So, how to you set up the code? Well, try this first and then look at the code:

Click to Print This Page

And here's the code:

Click to Print This Page

The JavaScript is triggered by using the command "javascript:" in place of a URL, thus allowing a hypertext link to fire off the JavaScript.

And before you ask, yep, this will work with almost any JavaScript Event Handler as long as the format allows you to write it as a specific line or trigger a function.

You can set it to print off of an image:

It looks like this as an image:

And yes, you can set it to trigger off a button:

It looks like this as a form button:

To make sure that your visitors have JavaScript enabled before you provide them with a button that only works using JavaScript, you can use JavaScript to print out the button. That way if they have it disabled, no button appears, saving them the frustration of clicking a button that does absolutely nothing:

Some Printing Suggestions

Okay, now you have the power to force a print request, but that doesn't mean to simply offer a print on any page. You should make a point of being helpful to your users.

• Make a Page for Printing - The Goodies tutorials, as you can see, have a wavy background, a bunch of images, and stuff stuck in all over the place. They're not very good for printing. If I was to offer this page for printing, I would make a point of creating basically a text-only page, or at least a page with limited images and no background.
• Make the Page Printer-Friendly - I would lose all text colors and make sure the width of the page was no more than 500px, left justified, so that what was shown on the screen would print on the printed page in the same way.
• Allow the User to Choose to Print - My print offering would be text at the bottom or an icon that doesn't intrude. Everyone knows they can already print your pages without any help from your coding. So, make this something you offer as a help rather than putting it into people's faces.
• Never Attach This to a User Event - Don't set this to an onLoad, or an onMouseOver, or some other user event. It will only tend to upset those who have visited your page and probably stop them from coming back.

How to Session time out when computer got ideal

Session time must be set in web.config file

Use tag for session timeout Property
important : Remeber 10000 in seconds....


Use Session_End Event which is available in Global.asax file.....

Repeator Control in Asp.net

The Repeater control is used to display a repeated list of items that are bound to the control. The Repeater control may be bound to a database table, an XML file, or another list of items. Here we will show how to bind an XML file to a Repeater control.