1 / 25

Module 10 Merging Data and Passing Tables

Module 10 Merging Data and Passing Tables. Module Overview. Using the MERGE Statement Implementing Table Types Using Table Types As Parameters. Lesson 1: Using the MERGE Statement. MERGE Statement WHEN MATCHED WHEN NOT MATCHED BY TARGET WHEN NOT MATCHED BY SOURCE

hallam
Download Presentation

Module 10 Merging Data and Passing Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 10 Merging Data and Passing Tables

  2. Module Overview • Using the MERGE Statement • Implementing Table Types • Using Table Types As Parameters

  3. Lesson 1: Using the MERGE Statement • MERGE Statement • WHEN MATCHED • WHEN NOT MATCHED BY TARGET • WHEN NOT MATCHED BY SOURCE • OUTPUT Clause and $action • MERGE Determinism and Performance • Demonstration 1A: MERGE Statement

  4. MERGE Statement • Modifies data in a target table (or updatable view or CTE) based on the results of a join with a source table • Commonly used to populate data warehouses • INSERT data if not already present • UPDATE data if already present • Target table plus a source rowset • Must specify how the source and target are joined MERGEINTOdbo.EmployeeAS e USINGdbo.EmployeeUpdateASeu ONe.EmployeeID=eu.EmployeeID …

  5. WHEN MATCHED • Clause that defines the action to be taken when the row in the source is found in the target • Specifies the data modifications to take place – can be INSERT, UPDATE or DELETE • Two WHEN MATCHED clauses can be included – needs an extra predicate on the first • WHEN MATCHED AND s.Quantity > 0 • One must be an UPDATE, the other a DELETE MERGEINTOdbo.EmployeeAS e USINGdbo.EmployeeUpdateASeu ONe.EmployeeID=eu.EmployeeID WHENMATCHEDTHEN UPDATESETe.FullName=eu.FullName, e.EmploymentStatus=eu.EmploymentStatus

  6. WHEN NOT MATCHED BY TARGET • Clause that defines the action to be taken when the row in the source cannot be found in the target • The words BY TARGET are optional and often omitted MERGEINTOdbo.EmployeeAS e USINGdbo.EmployeeUpdateASeu ONe.EmployeeID=eu.EmployeeID WHENMATCHEDTHEN UPDATESETe.FullName=eu.FullName, e.EmploymentStatus=eu.EmploymentStatus WHENNOTMATCHEDTHEN INSERT (EmployeeID,FullName,EmploymentStatus) VALUES (eu.EmployeeID,eu.FullName,eu.EmploymentStatus);

  7. WHEN NOT MATCHED BY SOURCE • Clause that defines the action to be taken for rows in the target that were not supplied in the source • Not commonly used but typically involve a DELETE MERGEINTOdbo.EmployeeAS e USINGdbo.EmployeeUpdateASeu ONe.EmployeeID=eu.EmployeeID WHENMATCHEDTHEN UPDATESETe.FullName=eu.FullName, e.EmploymentStatus=eu.EmploymentStatus WHENNOTMATCHEDTHEN INSERT (EmployeeID,FullName,EmploymentStatus) VALUES (eu.EmployeeID,eu.FullName,eu.EmploymentStatus) WHENNOTMATCHEDBYSOURCETHEN DELETE;

  8. OUTPUT Clause and $action • OUTPUT clause can also be used with MERGE • $action used to determine the action taken for each row MERGEINTOdbo.EmployeeAS e USINGdbo.EmployeeUpdateASeu ONe.EmployeeID=eu.EmployeeID WHENMATCHEDTHEN UPDATESETe.FullName=eu.FullName, e.EmploymentStatus=eu.EmploymentStatus WHENNOTMATCHEDTHEN INSERT (EmployeeID,FullName,EmploymentStatus) VALUES (eu.EmployeeID,eu.FullName,eu.EmploymentStatus) OUTPUT $action,inserted.EmployeeID,deleted.EmployeeID;

  9. MERGE Determinism and Performance • Matching source and destination rows using MERGE is not the same as matching with UPDATE • UPDATE can allow a source row to update multiple target rows • MERGE joins must be deterministic – an incoming row must only match a single target row • MERGE performs well as it makes only a single pass through the data • Often better performance than separate INSERT, UPDATE and DELETE statements

  10. Demonstration 1A: MERGE Statement In this demonstration you will see: • How to use the MERGE statement • How to use the OUTPUT clause with the MERGE statement • How to perform optional updates with MERGE • How to use MERGE as a composable query • How to use the VALUES clause as a MERGE source

  11. Lesson 2: Implementing Table Types • Reducing Round-Trip Overhead • Options for Passing Lists as Parameters • Demonstration 2A: Passing Delimited Lists • Introduction to the TABLE Type • Populating TABLE Types with Row Constructors • Demonstration 2B: TABLE Types and Row Constructors

  12. Reducing Round-Trip Overhead • Round-trip to from an application to SQL Server and back can be substantial • Common aim to minimize the impact of round trips • Many operations include many round-trips and cause transactions to last longer than desirable • Add an order • Start Transaction • Save Order Header • Save Order Detail Line 1 • Save Order Detail Line 2 • Save Order Detail Line 3 • Commit Transaction

  13. Options for Passing Lists as Parameters • Prior to SQL Server 2008, options for passing lists of values in a single call were limited • Delimited Lists (often comma-delimited) • Required writing custom parsing logic • Loose structure • Loose data typing • XML • Also requires some complex parsing logic • Semi-structured • Loose data typing without schemas

  14. Demonstration 2A: Passing Delimited Lists In this demonstration, you will see • How to query a table-valued function that performs list parsing • How the function allows for different delimiters • How to use the function in a join • How common errors can occur with delimited lists

  15. Introduction to the TABLE Type • Earlier versions of SQL Server allowed variables to be of type TABLE and to allow definition of the structure of the table • SQL Server allows the creation of table data type definitions • Can be used for variables • Can be used for parameters CREATETYPEdbo.CustomerBalance ASTABLE (CustomerIDint, CurrentBalancedecimal(18,2)); GO

  16. Populating TABLE Types with Row Constructors • SQL Server allows the population of multiple rows of data into a table variable via row constructors • Can be used with both INSERT and MERGE statements • Multi-row inserts are atomic operations • All inserts occur or none occur DECLARE @Balance dbo.CustomerBalance; INSERTINTO @Balance VALUES (12,14.50),(15,13.75),(22,19.50);

  17. Demonstration 2B: TABLE Types and Row Constructors In this demonstration you will see: • How to work with row constructors • How to declare a table data type • How to work with variables with user-defined table data types

  18. Lesson 3: Using TABLE Types As Parameters • TABLE Input Parameters for Stored Procedures • Using Row Constructors to Populate Parameters • Demonstration 3A: Passing Tables to Stored Procedures

  19. TABLE Input Parameters for Stored Procedures • User-defined table data types can be used as input parameters for stored procedures • Not able to be used for output parameters • Must include READONLY in the definition CREATETYPEdbo.SalesDetailsASTABLE (Descriptionvarchar(50), Price decimal(18,2) ); GO CREATEPROCEDUREdbo.SalesInsert @CustomerIDint, @PurchaseOrderNumbervarchar(20), @SalesDetailsdbo.SalesDetailsREADONLY, @SaleIDintOUTPUT ASBEGIN

  20. Using Row Constructors to Populate Parameters • Table variables are created to provide the parameters to pass to stored procedures with table-valued parameters • Row constructors are ideal for populating the table variables prior to making the stored procedure call DECLARE @SaleIDint; DECLARE @SalesDetailsdbo.SalesDetails; INSERTINTO @SalesDetails VALUES('Product 1',12.3), ('Product 2',14.66), ('Product 3',122.35); EXECdbo.SalesInsert 12,'BigOrder',@SalesDetails,@SaleID OUTPUT;

  21. Demonstration 3A: Passing Tables to Stored Procedures In this demonstration you will see: • How traditional stored procedure calls often involve multiple round trips to the server • How to declare a table data type • How to use the table data type to avoid round trips • How to view catalog information about the table data types by querying the sys.types and sys.table_types system views

  22. Lab 10: Passing Tables and Merging Data • Exercise 1: Create a Table Type • Exercise 2: Use a Table Type Parameter • Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits) Logon information Estimated time: 45minutes

  23. Lab Scenario In earlier versions of SQL Server, passing lists of values to stored procedures was a challenge. SQL Server 2008 introduced the table type and table-valued parameters. In this lab, you will create a replacement stored procedure Reports.GetProductsByColorList_Testthat uses a table-valued parameter to replace an existing stored procedure Reports.GetProductsByColorList that was based on passing a comma-delimited list of values. You will then create a new procedure that processes complete rows of data and performs updates using the MERGE statement.

  24. Lab Review • What is the purpose of the OUTPUT clause? • In the values returned by an OUTPUT clause, how can we tell if an INSERT, UPDATE or DELETE occurred?

  25. Module Review and Takeaways • Review Questions • Best Practices

More Related