Retrieving a Dataset from View State (Visual C#)

If you want dataset on same page and with post back it not destroy use viewstate      

DataSet ds = new DataSet();
            ViewState["ViewStateDateSet"] = ds;//Insert Data Set in View State


            DataSet ds1 = (DataSet)ViewState["ViewStateDateSet"]; // Retrive DataSet from ViewState

Using Common Table Expressions(CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

e.g.-

;with SelectCta(Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID) as
(
SELECT DISTINCT
                         IE_BundleNo.Bundle_No, IE_BundleNo.User_Id, CONVERT(varchar, IE_BundleNo.Alloted_Date, 103) AS AllotDate, UserLogin.UserName, IE_BundleNo.NoofBills,
                         IE_BundleNo.Net_Amt, IE_BundleNo.Claim_Amt, IE_BundleNo.Status, IE_BundleNo.Alloted_Date, IE_BundleNo.BundleNoText, IE_BillAllotStatus.AllotedUserId,
                        (select DISTINCT InsurerComName from IE_Master_InsuranceCompany where IE_Master_InsuranceCompany.InsurerComID=IE_BillAllotStatus.TPAID)  AS TPA,
'From ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateFrom, 103) + ' To ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateTo, 103) AS billdated,
IE_BillAllotStatus.TPAID
FROM            IE_BundleNo INNER JOIN
                         UserLogin ON IE_BundleNo.User_Id = UserLogin.UserID INNER JOIN
                         IE_BillAllotStatus ON IE_BundleNo.BundleNoText = IE_BillAllotStatus.BundleNoText
)
select Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID,
dbo.Get_Insurencecompany(TPAID,Bundle_No ) as InsCompanyName
 from SelectCta

Merge Statement in SQL Server

Many a times we came across situation to merge records between two tables. It will be like incremental update i.e., add new records and update existing records based on a reference column.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_ matched> ];

e.g.-
Merge into  EmployeeBulk1 as tab1
using(select * from EmployeeBulk2) as tab2
on tab1.employeeid=tab2.employeeid
when matched then
update set tab1.employeename=tab2.employeename,
    tab1.employeedepartment=tab2.employeedepartment,
    tab1.Company=tab2.company
when not matched then
insert values(tab2.employeeid,tab2.employeename,tab2.employeedepartment,tab2.company);