RSS

Tag Archives: Warehouse

MERGE statement in SQL Server 2008

Introduction

In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement.

Using of MERGE statement

  • Conditionally insert or update rows in a single target table using a single statement which reduce number of code lines needed to perform that
  • Synchronize two tables specially in Data warehouse.

    Merge Syntax

    1.  
    2. The MERGE syntax consists of five primary clauses:

    3. The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
    4. The USING clause specifies the data source being joined with the target.
    5. The ON clause specifies the join conditions to determine where the target and source match.
    6. The WHEN clauses specify the actions to take based on the results of the ON clause.

      The OUTPUT clause returns a row for each row in the target object that is updated, inserted, or deleted.

    Example

    1. Create Folder named db on c:\
    2. Create New Database Named TestDb
    3. Create New table named SrcTable
    4. Create New other table named DestTable
    5. Insert New Records in SrcTable
    6. Insert New Records in DestTable
    7. Use Merge Statement to Synchronize DestTable to SrcTable which insert, update or delete from target table to map to source table

    Example Code (Copy and Run script bellow on SQL Server Managment Studio)

    USE [master]

    GO

    /********************************************

    ———-1)Create TestDb——————–

    ********************************************/

    CREATE DATABASE [TestDb] ON PRIMARY

    ( NAME = N’TestDb_Data’, FILENAME = N’C:\db\TestDb_Data.mdf’ , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )

    LOG ON

    ( NAME = N’TestDb_Log’, FILENAME = N’C:\db\TestDb_Log.ldf’ , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )

    GO

    use [TestDb];

    go

    /********************************************

    ———-2)Create SrcTable—————–

    ********************************************/

    CREATE TABLE [dbo].[SrcTable](

    [SrcId] [int] NOT NULL,

    [SrcName] [nvarchar](50) NULL

    )

    go

    /********************************************

    ———-3)Create DestTable—————–

    ********************************************/

    CREATE TABLE [dbo].[DestTable](

    [DestId] [int]NOT NULL,

    [DestName] [nvarchar](50) NULL

    )

    GO

    /********************************************

    ——-4)Insert Records to SrcTable———-

    ********************************************/

    Insert into SrcTable values(1,‘SrcTest1’)

    Insert into SrcTable values(2,‘SrcTest2’)

    Insert into SrcTable values(5,‘SrcTest3’)

    go

    /********************************************

    ——-5)Insert Records to DestTable———-

    ********************************************/

    Insert into DestTable values(1,‘SrcTest1’)

    Insert into DestTable values(2,‘DestTest2’)

    Insert into DestTable values(3,‘DestTest3’)

    Insert into DestTable values(4,‘DestTest4’)

    go

    /********************************************

    6)Use Merge Statement to Syncronize DestTable to SrcTable

    ********************************************/

    –View DestTable and DestTable before Merge

    Select * From SrcTable

    Select * From DestTable

    go

    –Target table which we want to perform on it insert, Update and delete statement

    Merge DestTable as Target

    –Source Table which we want to join it with Taget Table

    using(Select SrcId,SrcName From SrcTable)as Source

    –Join Condition between target and Source

    on (Target.DestId=Source.SrcId)

    when Matched and Target.DestName<>Source.SrcName then

    Update Set Target.DestId=Source.SrcId, Target.DestName=Source.SrcName

    when Not Matched by Target then

    Insert (DestId,DestName) values(Source.SrcId,Source.SrcName)

    When Not Matched by source then

    delete

    OUTPUT $action, Inserted.*, Deleted.*;

    go

    –View SrcTable and DestTable after Merge

    Select * From SrcTable

    Select * From DestTable

    go

     

     

  • Advertisements
     
    Leave a comment

    Posted by on December 26, 2009 in SQLServer, T-SQL, Warehouse

     

    Tags: , ,

    SQLHero Dec Session – Microsoft Egypt

    SQLHero delivered June Session in Microsoft SQL Server 2008

    Topic Microsoft SQL Server 2008
    Date Dec 29, 2009
    Time 10:00 – 3:00
    Location Microsoft, Smart Village
    Presenter Ahmed Mosa
    Presentation download
     

    Tags: , , , , , , ,

    SQLHero Nov Session – Microsoft Egypt

    SQLHero delivered June Session in SQL Enterprise Reporting (SQL2008)

    Topic SQL Enterprise Reporting (SQL2008)
    Date Nov 19, 2009
    Time 10:00 – 3:00
    Location Microsoft, Smart Village
    Presenter Ahmed Mosa
    Presentation download
     

    Tags: , , , , ,

    SQLHero Oct Session – Microsoft Egypt

    SQLHero delivered June Session in SQL Enterprise Reporting (SQL2008)

    Topic SQL Enterprise Reporting (SQL2008)
    Date Oct 29, 2009
    Time 10:00 – 3:00
    Location Microsoft, Smart Village
    Presenter Ahmed Mosa
    Presentation download
     

    Tags: , , , , , ,

    Partition Table in SQL Server 2008

    1. What is Partition table?

    Data is partitioned horizontally by range

    Partition Table

    2. How to Create Partition Table?

    You can create partition table by 2 ways

    1. programmability
    2. wizard in SQL Server 2008 New

    But you must understand 2 things before creation Partition Tables

    Partition Function: Partition functions define partition boundaries and Boundary values can be assigned to LEFT or RIGHT

    clip_image004

    Partition Scheme: A partition scheme assigns partitions to filegroups

    clip_image006

    3. How to Create Partition Table by wizard in SQL Server 2008?

    Right click on table then go to storage then select Create Partition as in fig bellow

    1

    Select a column you want to use it in partition as in fig bellow

    2

    Create Partition function as in fig bellow

    3

    Create Partition Scheme as in fig bellow

    4

    Determine ranges as in fig bellow

    5

    Create Partition by select run immediately as in fig bellow

    6    

    Congratulation your table now partitioned

     

    4. How to Create Partition Table programmability?

     

     

    /***************************************************

    1)Create database TestDb

    ****************************************************/

    CREATE DATABASE [TestDb] ON PRIMARY

    ( NAME = N’TestDb’, FILENAME = N’C:\db\TestDb.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

    FILEGROUP [fg1]

    ( NAME = N’f1′, FILENAME = N’C:\db\f1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

    FILEGROUP [fg2]

    ( NAME = N’f2′, FILENAME = N’C:\db\f2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

    FILEGROUP [fg3]

    ( NAME = N’f3′, FILENAME = N’C:\db\f3.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

    FILEGROUP [fg4]

    ( NAME = N’f4′, FILENAME = N’C:\db\f4.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

    LOG ON

    ( NAME = N’TestDb_log’, FILENAME = N’C:\db\TestDb_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

    GO

    USE [TestDb]

    GO

    /***************************************************

    3)Create Partition Function

    ****************************************************/

    Create Partition Function pf_Date(Date)

    as range right

    For values(’01/01/2006′,’01/01/2007′,’01/01/2008′)

    go

    /***************************************************

    3)Create Partition scheme

    ****************************************************/

    Create Partition Scheme ps_Date

    AS Partition pf_Date

    to(fg1,fg2,fg3,fg4)

    go

    /***************************************************

    3)Create Partition Contact Table

    ****************************************************/

    CREATE TABLE [dbo].[Contact](

    [ContactId] [int] IDENTITY(1,1) NOT NULL,

    [FullName] [nvarchar](50) NULL,

    [CreationDate] [date] NULL

    )on ps_Date(CreationDate)

    GO

    /***************************************************

    4)Insert rows to table

    ****************************************************/

    insert into dbo.Contact values(‘amosa’,’01/01/2006′)

    insert into dbo.Contact values(‘mmosa’,’01/01/2007′)

    insert into dbo.Contact values(‘zemo’,’01/01/2008′)

    insert into dbo.Contact values(‘test1′,’05/01/2005’)

    insert into dbo.Contact values(‘test2′,’05/01/2006’)

    insert into dbo.Contact values(‘test3′,’05/01/2007’)

    insert into dbo.Contact values(‘test4′,’05/01/2008’)

    insert into dbo.Contact values(‘test5′,’05/01/2009’)

    go

    /***************************************************

    5)Query Table to know where did rows save

    ****************************************************/

    Select *,$partition.pf_Date(CreationDate)FileGroupNo From dbo.Contact 

     
     

    Tags: , , , ,

    SQL Server 2008 Compare Edition Features

    to find full comparison of key capabilities between SQL Server 2008 Enterprise, Standard, Workgroup, Web and Express editions

    Just click on Microsoft

     

    Tags: , , , ,

    Recursive using Common Table Expressions (CTE)

    Introduction

    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.

    A CTE can be used to

  • Create a recursive query (Current Example)
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

    Syntax of CTE

    with CTE_Name(Col1,Col2,Col3..)
    as (
    –Anchor Query
    Union All
    –Recursive member is defined referencing cte_name
    )
    Select * From CTE_Name

    Example

    use AdventureWorks

    go

     

    with CTE_EmployeeLevel(EmployeeId,FullName,ManagerId,ManagerName,EmpLevel)

    as

    (

    select

    EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= EmployeeID)FullName,

    ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= ManagerID)ManagerName,

    1

    From HumanResources.Employee

    where ManagerId Is Null

     

    union all

     

    Select

    tbEmp.EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.EmployeeID)FullName,

    tbEmp.ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.ManagerID)ManagerName,

    EmpLevel+1

    From HumanResources.Employee tbEmp inner join CTE_EmployeeLevel CTEEmp

    on tbEmp.ManagerID=CTEEmp.EmployeeId

    )

     

    Select * From CTE_EmployeeLevel

    Exam
    ple Code Walkthrough

    when Anchor query run the result set will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    Stephanie Bourne 109 NULL NULL 1

    then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    6 Frances Adams 109 Stephanie Bourne 2

    we will repeat step before again Then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    2 Catherine Abel 6 Frances Adams 3
    46 Stephen Ayers 6 Frances Adams 3
    106 Corinna Bolender 6 Frances Adams 3
    119 Christopher Bright 6 Frances Adams 3

    we will repeat step before again Then we will take Employee Id from above result set then join employee id from Employee if no result

    CTE start join all result set to be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    Stephanie Bourne 109 NULL NULL 1
    6 Frances Adams 109 Stephanie Bourne 2
    2 Catherine Abel 6 Frances Adams 3
    46 Stephen Ayers 6 Frances Adams 3
    106 Corinna Bolender 6 Frances Adams 3
    119 Christopher Bright 6 Frances Adams 3
  •  

    Tags: , , ,

     
    %d bloggers like this: