IQ

 SQL Server MERGE

Suppose, you have two table called source and target tables, and you need to update the target table based on the values matched from the source table. There are three cases:

1. The source table has some rows that do not exist in the target table. In this case, you need to insert rows that are in the source table into the target table.
2. The target table has some rows that do not exist in the source table. In this case, you need to delete rows from the target table.
3. The source table has some rows with the same keys as the rows in the target table. However, these rows have different values in the non-key columns. In this case, you need to update the rows in the target table with the values coming from the source table.

The following picture illustrates the source and target tables with the corresponding actions: insert, update, and delete:

If you use the INSERT, UPDATE, and DELETE statement individually, you have to construct three separate statements to update the data to the target table with the matching rows from the source table.

However, SQL Server provides the MERGE statement that allows you to perform three actions at the same time. The following shows the syntax of the MERGE statement:

MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
    THEN update_statement
WHEN NOT MATCHED
    THEN insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;Example:Suppose we have two table sales.category and sales.category_staging that store the sales by product category


MERGE sales.category t 
    USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;
2) CTE RECURSIVE QUERY TO GET EMPLOYEE MANAGER HIERARCHY WITH LEVEL


WITH EMP_CTE AS
(
SELECT EmployeeId, EmployeeName, ManagerId, CAST('' AS VARCHAR(50)) ManagerName, 0 AS EmployeeLevel FROMtbEmployee WHERE ManagerId IS NULL
UNION ALL
SELECT T.EmployeeId,T.EmployeeName, T.ManagerId,CAST(C.EmployeeName AS VARCHAR(50)) ManagerName,EmployeeLevel + 1 AS EmployeeLevel FROM tbEmployee AS T
INNER JOIN EMP_CTE  AS C ON C.EmployeeId=T.ManagerId
)
SELECT * FROM EMP_CTE


Explanation: The base record for the CTE is obtained by the first select query above UNION ALL. It gets all EmployeeId which don’t have ManagerId ie. NULL value. This means they are the top most employees of the organization so their Employee Level is set to 0.

Second select query below UNION ALL is executed recursively to get results and it will continue until it returns no rows. E.g. Result will have EmployeeIds which have ManagerId (i.e, EmployeeId of the first result).  This is obtained by joining our CTE result with tbEmployee table on columns EmployeeId of CTE with ManagerId of table tbEmployee.

3) HTTP response status codes
Informational responses (100–199)
Successful responses (200–299)
Redirects (300–399)
Server errors (500–599)
Client errors (400–499)
4) Difference between List and IEnumerable
IEnumerable is read-only and List is not.
So if you need the ability to make permanent changes of any kind to your collection (add & remove), you'll need List. If you just need to read, sort and/or filter your collection, IEnumerable is sufficient for that purpose.
So in your practical example, if you wanted to add the four strings one at a time, you'd need List. But if you were instantiating your collection all at once, you could use IEnumerable.
5) Anonymous Method
An anonymous method is a method without a name. Anonymous methods in C# can be defined using the delegate keyword and can be assigned to a variable of delegate type.
public delegate void Print(int value);

static void Main(string[] args)
{
    Print print = delegate(int val) { 
        Console.WriteLine("Inside Anonymous method. Value: {0}", val); 
    };

    print(100);
}
6) Difference between lambda and LINQ?

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic
A lambda expression is an anonymous function that you can use to create delegates or expression tree types. By using lambda expressions, you can write local functions that can be passed as arguments or returned as the value of function calls.
Linq uses Lambda expression in order to execute some of its functionalities.
Example:
new [] { "Dan", "Yossi", "Ben" }.Where(item => item.Length == 3);
Lambda expression: item => item.Length == 3
Linq: (from item in (new [] { "Dan", "Yossi", "Ben" }) where item.Length == 3)