Merge Statement SQL Server 2008

MERGE statement is introduced in SQL Server 2008 that can greatly enhance the functionality for Data Warehouse and the staging servers.

The Merge statement allows you to perform DML actions on target table based on whether
or not a row matches information found in source table.

A common Merge scenario is moving data from one table to another.

Like User and UserHistory

User Table :

CREATE TABLE [dbo].[User]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Is_Active] [bit] NULL,
[CREATE_DATE] [date] NOT NULL
)

GO

ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CREATE_DATE] DEFAULT (getdate()) FOR [CREATE_DATE]

GO

AND
UserHistory Table is like:

CREATE TABLE UserHistory
(
HistoryId INT IDENTITY(1, 1),
Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Is_Active BIT NULL,
Create_Date DATE NOT NULL
)

------------------------------------------------------------------------------------

Now insert some data in user table and try running this query more than once and look
on the results that are produced by OUTPUT and $action clauses:

MERGE INTO UserHistory AS UH
USING [User] as U
ON U.Id = UH.Id
WHEN MATCHED THEN
UPDATE SET UH.Id = U.Id, UH.Name = U.Name
,UH.Is_Active = U.Is_Active, UH.Create_date = U.Create_date
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Age, Is_Active, Create_Date)
VALUES (Id, Name, Age, Is_Active, Create_Date)
WHEN NOT MATCHED BY SOURCE THEN
Update SET IS_ACTIVE = 0
Output $action, inserted.*;

-------------------------------------------------------------------------------------
You can perform insert, update and delete operation by matching the Source Table (User)
and the Target Table (UserHistory) in a single operation and can have the details about the
action from OUTPUT clause and using $action you can know the operation performed

SQL Server 2008:Output Clause Sample

So with SQL Server 2008 we have output clause available inside the query that means we can access the inserted, deleted or updated value form the output clause inside a Stored Procedure and we need not to write a trigger

Ex.
Suppose we have a user table on inserting the data we need to insert the same value to a table type variable, we can accomplish this task as given below:

DECLARE @User TABLE (ID INT, Name VARCHAR(50), Age INT, Is_Active BIT, CREATE_DATE DATETIME)
INSERT INTO [User] (Name, Age, Is_Active, CREATE_DATE)
OUTPUT inserted.* INTO @User VALUES ('TESTING', '26', 1, GETDATE())

SELECT * FROM @User

Create class variables and properties from database table

Use the below given query to create variables and properties from your schema.

Just replace the @tableName variable with the table name for which you want to generate the variables and properties.

This makes use of systypes and syscolumns system defined views to get the name of columns from the table and their type. Also add more types as per your requirement.

-----------------------------------------------------------------------------

declare @tableName varchar(50)

set @tableName = 'tbTest'

SELECT 'private ' + (

case systypes.name

when 'varchar' then 'string'

when 'decimal' then 'decimal'

when 'datetime' then 'DateTime'

when 'int' then 'int'

else systypes.name end) + ' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ';'

FROM syscolumns inner join systypes on

syscolumns.xtype = systypes.xtype

where id = Object_ID(@tableName)

order by colorder

SELECT 'public ' + (

case systypes.name

when 'varchar' then 'string'

when 'decimal' then 'decimal'

when 'datetime' then 'DateTime'

when 'int' then 'int'

else systypes.name end)+ ' ' + syscolumns.name + char(10) +

'{' + char(10) + 'get { return' +

' _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + '; }'

+ char(10) + 'set { _' + lower(substring(syscolumns.name, 1, 1)) + right(syscolumns.name, len(syscolumns.name) -1) + ' = value; }' + char(10) + '}'

FROM syscolumns inner join systypes on

syscolumns.xtype = systypes.xtype

where id = Object_ID(@tableName)

order by colorder

-----------------------------------------------------------------------------

SQL Server 2008 Features

What's New in Sql Server 2008

Power of Reflection Emit

Some time ago I came across a situation where the end user of my application was willing to add new types at his own. Obviously I was not having any other solution rather using Reflection Emit.

It was great creating new types, dynamic methods at run time.

So as I did earlier similarly I will produce some code (to create new type (compiled class) with some properties and methods) out here with a little description.

But before working with Emit you should look on the MSIL Disassembler to know how the things looks alike after compilation and before JIT compiler processing.

------------------------------ Creating A Whole New Type ------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using DataFramework;
using System.Data;

namespace Generator
{
public class ClassGenerator
{
public ClassGenerator()
{

}

public void GenerateClass(string assemblyName, string className, List fieldWithType)
{
AssemblyName an = new AssemblyName(assemblyName);
AppDomain ad = AppDomain.CurrentDomain; // Use same app domain otherwise to use
// the generated classes you need to use
// remoting like - object handle unwrap
AssemblyBuilder ab = ad.DefineDynamicAssembly(an, AssemblyBuilderAccess.RunAndSave, AppDomain.CurrentDomain.BaseDirectory);

ModuleBuilder mb = ab.DefineDynamicModule(an.Name, "CustomClasses.dll");

TypeBuilder tb = mb.DefineType(className, TypeAttributes.Class | TypeAttributes.Public);

ConstructorInfo ci = tb.DefineDefaultConstructor(MethodAttributes.Public);

foreach (FieldStructure fieldName in fieldWithType)
{
Type type = GetType(fieldName.type);// Type.GetType(fieldWithType[fieldName], false, true);

FieldBuilder fb = tb.DefineField(fieldName.name, type, FieldAttributes.Private);

CreateProperty(tb, type, fb, fieldName.name);
}



Type generatedType = tb.CreateType();
ab.Save("CustomClasses." + className + ".dll");
}

#region Create Property for field
private void CreateProperty(TypeBuilder tb, Type fieldType, FieldBuilder fb, string key)
{
PropertyBuilder pb = tb.DefineProperty(SystemConstants.GetPropertyName(key), System.Reflection.PropertyAttributes.HasDefault, fieldType, Type.EmptyTypes);

MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig;

MethodBuilder mbGet = tb.DefineMethod(SystemConstants.getPrefix + key, getSetAttr, fieldType, Type.EmptyTypes);

ILGenerator getIL = mbGet.GetILGenerator();
// For an instance property, argument zero is the instance. Load the
// instance, then load the private field and return, leaving the
// field value on the stack.
getIL.Emit(OpCodes.Ldarg_0);
getIL.Emit(OpCodes.Ldfld, fb);
getIL.Emit(OpCodes.Ret);

MethodBuilder mbSet = tb.DefineMethod(SystemConstants.setPrefix + key, getSetAttr, null, new Type[] { fieldType });

ILGenerator setIL = mbSet.GetILGenerator();
// Load the instance and then the numeric argument, then store the
// argument in the field.
setIL.Emit(OpCodes.Ldarg_0);
setIL.Emit(OpCodes.Ldarg_1);
setIL.Emit(OpCodes.Stfld, fb);
setIL.Emit(OpCodes.Ret);

// Last, map the "get" and "set" accessor methods to the
// PropertyBuilder. The property is now complete.
pb.SetGetMethod(mbGet);
pb.SetSetMethod(mbSet);
}
#endregion

#region Get type based on string representation
private Type GetType(string typeName)
{
switch (typeName.ToLower())
{
case "string":
return typeof(String);
case "int32":
return typeof(Int32);
case "long":
return typeof(long);
case "float":
return typeof(float);
case "decimal":
return typeof(decimal);
default:
return typeof(object);
}
}
#endregion
}
}



----------------------- Helper Classes (SystemConstants)-----------------------------------
using System;
using System.Collections.Generic;
using System.Text;

namespace Generator
{
public class SystemConstants
{
public const string getPrefix = "get_";
public const string setPrefix = "set_";
public const string variablePrefix = "m_";
public const string idVariableForClasses = "ID";
public const string idVariableType = "int32";

private static bool IsFieldDicContainsId(Dictionary fieldWithType)
{
foreach (string fieldName in fieldWithType.Keys)
{
if (fieldName.ToUpper() == idVariableForClasses)
{
return true;
}
}
return false;
}

public static Dictionary GetFinalDictionary(Dictionary fieldWithType)
{
if (IsFieldDicContainsId(fieldWithType))
{
if (fieldWithType.ContainsKey(idVariableForClasses))
{
fieldWithType.Remove(idVariableForClasses);
}
}

return fieldWithType;
}

internal static string GetPropertyName(string key)
{
// Implement Logic to create property like m_name ==> Name right now its NAME
return key.ToUpper();
}
}
}

----------------------- Helper Structure (FieldStructure)-----------------------------------

using System;
using System.Collections.Generic;
using System.Text;

namespace Generator
{
public struct FieldStructure
{
public string name;
public string type;
public bool isIdentity;
public bool isReference;
public string referenceClassName;
public string referenceColumnName;

}
}



--------------------------------- Code to be called from UI ------------------------------------

ClassGenerator cg = new ClassGenerator();

FieldStructure fStructA = new FieldStructure();
fStructA.name = "name";
fStructA.type = "string";
fStructA.isIdentity = false;

FieldStructure fStructB = new FieldStructure();
fStructB.name = "id";
fStructB.type = "int32";
fStructB.isIdentity = true;

List allFields = new List();
allFields.Add(fStructA);
allFields.Add(fStructB);

cg.GenerateClass("test", "Test", allFields);
}


Thats it. Try to use it, a .dll file will be saved in your application root directory, but beware if you are using web application and you intended to save any .dll file in the bin folder the application domain will reload and all your sessions will be lost as one of the application domain reloading is any change in the bin directory is.

For more reading you can look at this msdn link.

Let me know for your queries.
Shashank.... enjoy coding ....