SQL Developer Ramblings

Download Source Files: Here As promised in my first post , I’ve now put together an new example of how to use the the SQL MERGE statement, along with new TABLE Data Type to upsert a dimension table in a data warehouse completely within data flow.  In the previous example I used the control flow along with and recordset object to illustrate how to pass the data into a stored proc, however the call to the stored procedure was in the control flow. In this post I will use a script destination component to call the stored proc.

Download Source Files: Here In the first of what hope to be many posts. I’ve taken my first step into really utilising some of the many enhancements delivered in the most recent release of Microsoft SQL Server. Two of the most eagerly awaited enhancements to come out from Microsoft have been the advent of table data types and the new MERGE syntax. Table data types now allow SQL developers to pass table variables directly into stored procedures as a parameter, in the past ingenious way had been devised to do this with the use of temp tables or with XML, but table data types now make these process redundant and offer great performance improvements. For the data warehouse developers out there the MERGE statement now allows INSERT, UPDATE and DELETE statements to be applied to a table in a single pass, simplify the SCD (Slowly Changing Dimension) process.