Diffrence between CROSS APPLY and OUTER APPLY of SQL XML

CodeProject

Sample XML :
DECLARE @XML XML = 
'<Root>
    <UserInfo Id="1" Name="Name 1">
        <CityInfo Id="2" City="City 1" />
        <CityInfo Id="3" City="City 2" >
            <PrefInfo Id="33" Name="Show Whether" />
        </CityInfo>
    </UserInfo>
    <UserInfo Id="2" Name="Name 2">
        <CityInfo Id="4" City="City 3" />
        <CityInfo Id="5" City="City 4" >
            <PrefInfo Id="33" Name="Show Temprature" />
        </CityInfo>
    </UserInfo>
</Root>'

Cross Apply :  Cross apply is basically use inner join of two xml collection
SELECT
    UserInfo.value('@Id', 'BIGINT') as UserId
    , UserInfo.value('@Name', 'VARCHAR(20)') as UserName
    , CityInfo.value('@Id', 'BIGINT') as CityId
    , CityInfo.value('@City', 'VARCHAR(20)') as CityName
    , PrefInfo.value('@Id', 'BIGINT') as PrefId
    , PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
    CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
    CROSS APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)

Output :

UserId   UserName  CityId  CityName  PrefId   PrefName
-------- --------- ------- --------- -------- ----------------
1        Name 1    3       City 2    33       Show Whether
2        Name 2    5       City 4    33       Show Temprature 

Outer Apply :  Outer apply is basically use left outer join of two xml collection
SELECT
    UserInfo.value('@Id', 'BIGINT') as UserId
    , UserInfo.value('@Name', 'VARCHAR(20)') as UserName
    , CityInfo.value('@Id', 'BIGINT') as CityId
    , CityInfo.value('@City', 'VARCHAR(20)') as CityName
    , PrefInfo.value('@Id', 'BIGINT') as PrefId
    , PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
    CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
    OUTER APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)

Output :
UserId  UserName  CityId  CityName  PrefId  PrefName
------- --------- ------- --------- ------- ----------------
1       Name 1    2       City 1    NULL    NULL
1       Name 1    3       City 2    33      Show Whether
2       Name 2    4       City 3    NULL    NULL
2       Name 2    5       City 4    33      Show Temprature 

Rounding date in SQL query

Query : 
SELECT
    DATEADD( MI, DATEDIFF( MI, 0, DATEADD( SS, 30, Dates.SampleDate) ), 0) AS RoundedDate
FROM
(
    SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:29.998')
    UNION ALL
    SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:30.000')
) Dates

Results : 

RoundedDate
-----------------------
2011-08-29 12:59:00.000
2011-08-29 13:00:00.000

Use of Rollup, Grouping, Grouping_ID and Cube in Group By

ROLLUP = Generates the simple GROUP BY aggregate rows
  , plus subtotal or super-aggregate rows
  , and also a grand total row.
GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
GROUPING_ID = Is a function that computes the level of grouping.
CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.
Sample Data
DECLARE @Sales TABLE (Year INT, Quarter VARCHAR(50), SalesPerson VARCHAR(50), Amount FLOAT)

INSERT INTO @Sales values
      (1998, 'Q1', 'SalePerson1', 123)
    , (1998, 'Q2', 'SalePerson2', 234)
    , (1998, 'Q3', 'SalePerson4', 345)
    , (1998, 'Q4', 'SalePerson3', 556)
    , (1999, 'Q1', 'SalePerson1', 623)
    , (1999, 'Q2', 'SalePerson2', 734)
    , (1999, 'Q3', 'SalePerson3', 845)
    , (1999, 'Q4', 'SalePerson4', 956)

Example 1 : (Single field in grouping)

SELECT
     [Year]
    , AVG(Amount) as Average
    , GROUPING([Year]) as [YearRollUp?]
FROM 
    @Sales
GROUP BY
    [Year] WITH ROLLUP

Output :

Year        Average                YearRollUp?
----------- ---------------------- -----------
1998        314.5                  0
1999        789.5                  0
NULL        552                    1

Example 2 : (Multipal field in grouping)

SELECT
    [Year]
    , Quarter
    , AVG(Amount) as Average
    , GROUPING( [Year] ) as [YearRollUp?]
FROM 
    @Sales
GROUP BY
    GROUPING SETS (( [Year], Quarter), ( [Year] ), ())

Output : 

Year        Quarter Average                YearRollUp?
----------- ------- ---------------------- -----------
1998        Q1      123                    0
1998        Q2      234                    0
1998        Q3      345                    0
1998        Q4      556                    0
1998        NULL    314.5                  0
1999        Q1      623                    0
1999        Q2      734                    0
1999        Q3      845                    0
1999        Q4      956                    0
1999        NULL    789.5                  0
NULL        NULL    552                    1

Example 3 :( Example of Grouping_ID )

SELECT
    [Year]
    , Quarter
    , AVG(Amount) as Average
    , GROUPING_ID( [Year], Quarter) as [x]
FROM 
    @Sales
GROUP BY
    GROUPING SETS (( [Year], Quarter), ( [Year] ), ( [Quarter] ), ())

Output :

Year        Quarter Average                x
----------- ------- ---------------------- -----------
1998        Q1      123                    0
1999        Q1      623                    0
NULL        Q1      373                    2
1998        Q2      234                    0
1999        Q2      734                    0
NULL        Q2      484                    2
1998        Q3      345                    0
1999        Q3      845                    0
NULL        Q3      595                    2
1998        Q4      556                    0
1999        Q4      956                    0
NULL        Q4      756                    2
NULL        NULL    552                    3
1998        NULL    314.5                  1
1999        NULL    789.5                  1

Example 4:(Example of Cube)

SELECT
     [Year]
    , [Quarter]
    , AVG(Amount) as Average
    , GROUPING_ID( [Year], [Quarter]) as [x]
FROM 
    @Sales
GROUP BY
    CUBE( [Year], [Quarter])

Output :

Year        Quarter Average                x
----------- ------- ---------------------- -----------
1998        Q1      123                    0
1999        Q1      623                    0
NULL        Q1      373                    2
1998        Q2      234                    0
1999        Q2      734                    0
NULL        Q2      484                    2
1998        Q3      345                    0
1999        Q3      845                    0
NULL        Q3      595                    2
1998        Q4      556                    0
1999        Q4      956                    0
NULL        Q4      756                    2
NULL        NULL    552                    3
1998        NULL    314.5                  1
1999        NULL    789.5                  1

Query with static output

Query :

select * from
(
    values (1, 2), (2, 3), (3, 4)
) as MyTable(Col1, Col2)

Output :
Col1        Col2
----------- -----------
1           2
2           3
3           4

Get All Relationship of Primary Key

SELECT
    PKTableInfo.TABLE_NAME AS PK_Table_Name
    , ConstarintReference.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT_NAME
    , STUFF( (
                SELECT
                    ',' + kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS kcu
                WHERE kcu.TABLE_CATALOG = PKTableInfo.TABLE_CATALOG
                    AND kcu.TABLE_SCHEMA = PKTableInfo.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = PKTableInfo.TABLE_NAME
                    AND kcu.CONSTRAINT_CATALOG = PKTableInfo.CONSTRAINT_CATALOG
                    AND kcu.CONSTRAINT_SCHEMA = PKTableInfo.CONSTRAINT_SCHEMA
                    AND kcu.CONSTRAINT_NAME = PKTableInfo.CONSTRAINT_NAME
                ORDER BY kcu.ORDINAL_POSITION
                FOR XML PATH('')
            ), 1, 1, '') AS [PK_CONSTRAINT_COLUMNS]
    , FKTableInfo.TABLE_NAME AS FK_Table_Name
    , ConstarintReference.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    , STUFF( (
                SELECT
                    ',' + kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS kcu
                WHERE kcu.TABLE_CATALOG = FKTableInfo.TABLE_CATALOG
                    AND kcu.TABLE_SCHEMA = FKTableInfo.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = FKTableInfo.TABLE_NAME
                    AND kcu.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
                    AND kcu.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
                    AND kcu.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
                ORDER BY kcu.ORDINAL_POSITION
                FOR XML PATH('')
            ), 1, 1, '') AS [FK_CONSTRAINT_COLUMNS]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  AS ConstarintReference
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS PKTableInfo ON PKTableInfo.CONSTRAINT_CATALOG = ConstarintReference.UNIQUE_CONSTRAINT_CATALOG
        AND PKTableInfo.CONSTRAINT_SCHEMA = ConstarintReference.UNIQUE_CONSTRAINT_SCHEMA
        AND PKTableInfo.CONSTRAINT_NAME = ConstarintReference.UNIQUE_CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS FKTableInfo ON ConstarintReference.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
        AND ConstarintReference.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
        AND ConstarintReference.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
ORDER BY PK_Table_Name, FK_Table_Name
GO

Get detailed error handling information in SQL

Refer this link

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Enum Extention

Enum Extention :

    public static class EnumExtention
    {
        public static Dictionary<int, string> GetEnumData(this Type EnumType)
        {
            Dictionary<int, string> objEnumList = new Dictionary<int, string>();

            if (EnumType != null && EnumType.IsEnum)
            {
                foreach (int intCount in Enum.GetValues(EnumType))
                {
                    objEnumList.Add(intCount, Enum.GetName(EnumType, intCount).ToString().Replace("_", " "));
                }
            }
            return objEnumList;
        }
    }

Get Enum Data :

        private enum enumUserType
        {
            Admin,
            Sales_Department,
            Purchase_Department,
            Guest
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            ddlUserType.DataSource = typeof(enumUserType).GetEnumData();
            
            ddlUserType.DataTextField = "Value";
            ddlUserType.DataValueField = "Key";

            ddlUserType.DataBind();
        }

Output :

<select name="ddlUserType" id="ddlUserType">
    <option value="1">Admin</option>
    <option value="2">Sales Department</option>
    <option value="3">Purchase Department</option>
    <option value="4">Guest</option> 
</select>

OUTPUT Clause

SQL QUERY :

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblOutPut ( OutputAction VARCHAR(20), ID INT, GroupID INT, NewValueName VARCHAR(10), OldValueName VARCHAR(10) )
    
    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    
    --- Inserted
    BEGIN
        INSERT INTO #tblTarget
                OUTPUT 
                    'Inserted' AS OutputAction
                    , INSERTED.ID 
                    , INSERTED.GroupID
                    , INSERTED.Name
                    , NULL
                INTO #tblOutPut
            SELECT * FROM #tblSource
            UNION ALL
            SELECT ID + 10, GroupID, Name FROM #tblSource;
    END
    
    --- Updated
    BEGIN
        UPDATE #tblTarget
        SET Name = CAST(ID AS VARCHAR) + ' : ' + Name
        OUTPUT 
            'Updated' AS OutputAction
            , INSERTED.ID 
            , INSERTED.GroupID
            , INSERTED.Name
            , deleted.Name
        INTO #tblOutPut;
    END
    
    --- Deleted
    BEGIN
        DELETE FROM #tblTarget
            OUTPUT 
                'Deleted' AS OutputAction
                , deleted.ID 
                , deleted.GroupID
                , null
                , deleted.Name
            INTO #tblOutPut
        WHERE ID >= 10;
    END
    
    SELECT * FROM #tblOutPut

    DROP TABLE #tblSource
    DROP TABLE #tblTarget
    DROP TABLE #tblOutPut

Output :

OutputAction         ID          GroupID     NewValueName OldValueName
-------------------- ----------- ----------- ------------ ------------
Inserted             1           1           a11          NULL
Inserted             2           1           a21          NULL
Inserted             3           1           a31          NULL
Inserted             11          1           a11          NULL
Inserted             12          1           a21          NULL
Inserted             13          1           a31          NULL
Updated              1           1           1 : a11      a11
Updated              2           1           2 : a21      a21
Updated              3           1           3 : a31      a31
Updated              11          1           11 : a11     a11
Updated              12          1           12 : a21     a21
Updated              13          1           13 : a31     a31
Deleted              11          1           NULL         11 : a11
Deleted              12          1           NULL         12 : a21
Deleted              13          1           NULL         13 : a31

Merge Statement with insert into output table

    DECLARE @tblMergeOutput TABLE
    (
        ID                BIGINT IDENTITY(1,1)
        , MergeAction    NVARCHAR(100)
        , InsertedId    INT
        , DeletedId        INT
        , GroupID        INT
    )

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    INSERT INTO #tblSource VALUES (4,1,'a41')

    INSERT INTO #tblTarget VALUES (1,1,'a1')
    INSERT INTO #tblTarget VALUES (3,1,'a3')
    INSERT INTO #tblTarget VALUES (5,1,'a5')
    INSERT INTO #tblTarget VALUES (6,2,'b2')
    INSERT INTO #tblTarget VALUES (7,2,'b1')

    
    ---- Method 2 : Using Output insert into table
    BEGIN
        INSERT INTO @tblMergeOutput
            SELECT MergeAction, InsertedId, DeletedID, GroupID 
            FROM
            (
                MERGE #tblTarget
                USING (
                        SELECT *
                        FROM #tblSource
                    ) AS tblSource
                        ON tblSource.GroupID = #tblTarget.GroupID
                            AND tblSource.ID = #tblTarget.ID
                WHEN MATCHED THEN 
                    UPDATE
                    SET Name = tblSource.Name
                WHEN NOT MATCHED THEN
                    INSERT (
                                ID
                                , GroupID
                                , Name
                            )
                    VALUES ( 
                                tblSource.ID
                                , tblSource.GroupID
                                , tblSource.Name
                            )
                WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
                    THEN DELETE
                OUTPUT 
                    $action                AS MergeAction
                    , inserted.Id        AS InsertedId
                    , deleted.Id        AS DeletedID
                    , tblSource.GroupID    AS GroupID
            ) AS MergeOutput (MergeAction, InsertedId, DeletedID, GroupID);
    END

    SELECT * FROM #tblTarget order by GroupID, ID
    SELECT * FROM @tblMergeOutput

    DROP TABLE #tblSource
    DROP TABLE #tblTarget

Enable AD Hoc Distributed Queries OPENROWSET

By default Ad Hoc Distributed Queries are disabled on SQL server

If you try to run OPENROWSET you will get this error message

Server: Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


To enable it do this

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Openrowset Command in SQL

Syntax :

OPENROWSET 
( { 'provider_name', { 'datasource';'user_id';'password' 
   | 'provider_string' } 
   , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file', 
       { FORMATFILE ='format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE ='file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH =rows_per_batch ] 

Import CSV File :

SELECT 
    'EmployeeInfo.csv' AS FileName
    , '.csv' AS FileType
    , BulkColumn
FROM OPENROWSET(BULK N'C:\EmployeeInfo.csv', SINGLE_CLOB) AS Document;

Output :

FileName         FileType BulkColumn
---------------- -------- ----------------------------------------------
EmployeeInfo.csv .csv     Eugene Malarky,Grove Street Smithtown,New York

Example Of SQL Database :

SELECT JSDTest.*
FROM OPENROWSET('SQLOLEDB','PC\SQLSERVER2008';'sa';'softweb',
   'SELECT CategoryName, ProductName
   FROM JSDTest.dbo.Product AS Product
        INNER JOIN JSDTest.dbo.Category AS Category ON Category.CategoryID = Product.CategoryID
   ORDER BY CategoryName, ProductName') AS JSDTest

Example of MDB :

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) 
   AS a

Example of Excel :

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
    'SELECT * FROM [sheet1$]')

Import CSV File using SQL Query

SQL Query :

DECLARE @FileName VARCHAR(100) = 'C:\EmployeeInfo.csv'

CREATE TABLE #UserInfo
(
    UserName    VARCHAR(50)
    , Address    VARCHAR(100)
    , CITY        VARCHAR(10)
)

/*** Get Data From Import File ***/
DECLARE @SqlQuery VARCHAR(2000)
SELECT @SqlQuery =     'BULK INSERT'
                    + ' #UserInfo' 
                    + ' FROM'
                    + ' ''' + @FileName + ''' '
                    + ' WITH (FIELDTERMINATOR='','''
                    + ',ROWTERMINATOR = ''' + CHAR(10) + ''')'

EXECUTE ( @SqlQuery )
/*** Get Data From Import File ***/

SELECT * FROM #UserInfo

DROP TABLE #UserInfo

CSV File :

Eugene Malarky,Grove Street Smithtown,New York
Sam Adams,Main Street,Ashville
Loren Sandler,Main Street,Hometown

Output :

UserName                       Address                        CITY
------------------------------ ------------------------------ ----------
Eugene Malarky                 Grove Street Smithtown         New York

Sam Adams                      Main Street                    Ashville

Loren Sandler                  Main Street                    Hometown

MERGE Statement and compatibility level

In SQL 2008 Merge Statement when execute and give the error like this

Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

Solution
SQL Command

ALTER DATABASE <DBName>
SET COMPATIBILITY_LEVEL = 100 

Checking current user Menu Rights

SQL QUERY :

    SET NOCOUNT ON

    DECLARE @CurrentUserTypeID INT = 1  ------ <---- Pass Current User Type

    -----------------------------------
    DECLARE @tblUserType TABLE
    (
        UserTypeID            INT
        , TypeName        VARCHAR(50)
        , MenuRights    BIGINT
    )

    INSERT INTO @tblUserType 
    VALUES (1, 'Admin', 1)
        , (2, 'GenralManager', 2)
        , (3, 'SalesManager', 4)
        , (4, 'SalesMan', 8)
        , (5, 'Client', 16)

    DECLARE @tblMenu TABLE
    (
        MenuID            INT
        , MenuName        VARCHAR(50)
        , MenuRights    BIGINT
    )

    INSERT INTO @tblMenu 
    VALUES (1, 'Admin Setting', 1)
        , (2, 'Genral Setting', 3)
        , (3, 'Sales Report', 7)
        , (4, 'Sales Order Folloup', 15)
        , (5, 'Client FeedBack', 31)

    DECLARE @UserRights BIGINT = 0

    SELECT @UserRights = MenuRights
    FROM @tblUserType
    WHERE UserTypeID = @CurrentUserTypeID 

    SELECT * 
    FROM @tblMenu
    WHERE MenuRights & @UserRights = @UserRights

Admin Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
1           Admin Setting                                      1
2           Genral Setting                                     3
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

GenralManager Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
2           Genral Setting                                     3
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

SalesManager Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

Cross Apply in XML

Query :

 DECLARE @XML XML = 
 '<Customers>;
  <Customer ID="1">;
   <Address1>;Address - 11</Address1>;
   <Address2>;Address - 12</Address2>;
  </Customer>;
  <Customer ID="2">;
   <Address1>;Address - 21</Address1>;
   <Address2>;Address - 22</Address2>;
  </Customer>;
 </Customers>;'

 SELECT 
  Customer.value('@ID', 'INT') AS CustomerID
  , CustAdd1.value('.', 'VARCHAR(25)') Address1
  , CustAdd2.value('.', 'VARCHAR(25)') Address2
 FROM @XML.nodes('/Customers/Customer')a(Customer)
  CROSS APPLY Customer.nodes('Address1')aa(CustAdd1)
  CROSS APPLY Customer.nodes('Address2')bb(CustAdd2)

Output :
     ----------- ------------------------- -------------------------
     CustomerID  Address1                  Address2
     ----------- ------------------------- -------------------------
     1           Address - 11              Address - 12
     2           Address - 21              Address - 22
     ----------- ------------------------- -------------------------

Merge Statement

SQL Statement:

DECLARE @tblMergeOutput TABLE
 (
  ID                BIGINT IDENTITY(1,1)
  , MergeAction    NVARCHAR(100)
  , InsertedId    INT
  , DeletedId        INT
  , GroupID        INT
 )

 CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
 CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

 INSERT INTO #tblSource VALUES (1,1,'a11')
 INSERT INTO #tblSource VALUES (2,1,'a21')
 INSERT INTO #tblSource VALUES (3,1,'a31')
 INSERT INTO #tblSource VALUES (4,1,'a41')

 INSERT INTO #tblTarget VALUES (1,1,'a1')
 INSERT INTO #tblTarget VALUES (3,1,'a3')
 INSERT INTO #tblTarget VALUES (5,1,'a5')
 INSERT INTO #tblTarget VALUES (6,2,'b2')
 INSERT INTO #tblTarget VALUES (7,2,'b1')

 MERGE #tblTarget
 USING (
   SELECT *
   FROM #tblSource
  ) AS tblSource
   ON tblSource.GroupID = #tblTarget.GroupID
    AND tblSource.ID = #tblTarget.ID
 WHEN MATCHED THEN 
  UPDATE
  SET Name = tblSource.Name
 WHEN NOT MATCHED THEN
  INSERT (
     ID
     , GroupID
     , Name
    )
  VALUES ( 
     tblSource.ID
     , tblSource.GroupID
     , tblSource.Name
    )
 WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
  THEN DELETE
 OUTPUT 
  $action
  , inserted.Id
  , deleted.Id
  , tblSource.GroupID
 INTO @tblMergeOutput;

 SELECT * FROM #tblSource
 SELECT * FROM #tblTarget order by GroupID, ID
 SELECT * FROM @tblMergeOutput

 DROP TABLE #tblSource
 DROP TABLE #tblTarget

Target Table Output:

 ------------------
 ID  GroupID   Name
 ------------------
 1   1         a11
 2   1         a21
 3   1         a31
 4   1         a41
 6   2         b2
 7   2         b1
 ------------------

Merge Table Output:

 ----------------------------------------------------
 ID  Merge Action    InsertedID   DeletedID   GroupID
 ----------------------------------------------------
 1   INSERT          2            NULL        1
 2   INSERT          4            NULL        1
 3   UPDATE          1            1           1
 4   UPDATE          3            3           1
 5   DELETE          NULL         5           NULL
 ----------------------------------------------------

Convert Comma Seprate String to Row

DECLARE @TableA TABLE (Name VARCHAR(100) )
INSERT INTO @TableA VALUES ('MARY,MA,JENNY')

DECLARE @TableB TABLE (Name VARCHAR(100), DISTRICT_NO INT )
INSERT INTO @TableB VALUES ('MARY', 2)
INSERT INTO @TableB VALUES ('YY', 3)
INSERT INTO @TableB VALUES ('JOHN', 4)

DECLARE @XML XML

SELECT @XML = CAST( (
      SELECT '<name>' + REPLACE(Name, ',', '</name><name>') + '</name>'
      FROM @TableA
     ) AS XML )

SELECT *
FROM @TableB AS TableB
 INNER JOIN (
     SELECT
      TableA.value('.', 'VARCHAR(100)') AS Name
     FROM @XML.nodes('Name')e(TableA)
    ) AS TableA ON TableA.Name = TableB.Name