T-SQL: Create Dynamic SQL Statement using XMLPATH() and sp_executesql

In this post I would like to demonstrate a way how to exlude columns from a Select * query and executing the new query dynamically with the built-in procedure sp_executesql.
At work I had a request, where I had to exclude a certain column, which is usually no problem. The issue was, that I had a table that was being altered constantly and new columns where added to it from time to time. So simply writing the desired columns would have led to the result that new columns wouldn't be selected at runtime.
With the help of XMLPATH() and sp_executesql I was able to solve this issue. Let's look at the following table:

The table is called rec.DimCountries. Let's assume we will be adding a new column to it next week and another one a week later and so on. The end user would like to see every column except for CountryID.

rec.DimCountries
CountryID
Country
Name

With the help of sys.columns I am able to get all columns listed, but first we need to find out the object_id of the table in sys.objects

SELECT object_id
FROM sys.objects
WHERE name = 'DimCountries'

 object_id
-----------
517576882

Now we can lookup the column names from sys.columns with that object_id

select name
from sys.columns
where object_id = 517576882

 name
-----------
CountryID
Country
Name

So this gives us the chance to exclude 'CountryID' and lookup the rest dynamically.
I will add a comma for listing purposes here:

SELECT ', ' + Name
FROM sys.columns
where object_id = (
SELECT object_id
FROM sys.objects
WHERE name = 'DimCountries')
AND name <> 'CountryID'

-----------
, Country
, Name

I need to get this result above in one line and remove the first comma. For the removal I will use STUFF and for the one line listing I will use XMLPATH().
Here is the complete script. I will break down the main elements below:


DECLARE @cols nvarchar(200),
@sqlStatement nvarchar(300)

SELECT @cols = STUFF((
            SELECT ', ' + Name
            FROM sys.columns
            where object_id = (
                SELECT object_id
                FROM sys.objects
                WHERE name = 'DimCountries')
                AND name <> 'CountryID'
                FOR XML PATH('')
                ), 1, 1, '' )

SET @sqlStatement = N'SELECT ' + @cols + N' FROM rec.DimCountries'
EXEC sp_executesql @sqlStatement


FOR XML PATH('') will concat the results of the column name into 1 line

STUFF(', Country, Name', 1, 1, '') will remove the first comma. The first part has the value that needs to be changed, second part determines where to start, the third part is the length and fourth part is what to value to use for replacement:

STUFF ( character_expression , start , length , replaceWith_expression )


The output of @cols = Country, Name
So I will create a string for the SELECT part and assign it to variable @sqlStatement

Now we can hand over the variable @sqlStatement to sp_executesql
EXEC sp_executesql @sqlStatement 

Result delivers dynamically every column except CountryID
Country  Name
---------------------------
JPN      Japan
EU       European Union
A         Austria
ITA      Italy

SSIS: Slowly Changing Dimensions (SCD Type 2)

In SQL Server Data Tools (SSDT) there is an item to be found in the toolbox that can create a whole workflow of a table historization with the help of a wizard.

Before we start, a small reminder of the different types of Slowly Changing Dimensions (SCD). I will only cover the first 3 here:

SCD Type 0: You retain the original values as they are by time of insertion

SCD Type 1: Values are overwritten. There is no history here. You simply always store the most recent value

SCD Type 2: Each time the value changes, you add an additional row in the table. So basically you have multiple entries for one natural key but multiple surrogate keys

Now, let’s move to SSDT and open a new SSIS project.

In the Data Flow tab we will need a OLE DB Source and this be connected to the item Slowly Changing Dimensions

Now click through the wizard and define a target server and table and what will be the business key

In the next step we need to define the Change Type. The wizard offers three types – I will map them to the SCD types I described above:
Fixed Attribute -> SCD Type 0
Changing Attribute -> SCD Type 1
Historical Attribute -> SCD Type 2
Since I want to historize the data, I will select Historical attribute

In the next part, we will therefor also need a start and end date:

 

I defined 2 variables in my package, whereas I will use the End variable here.

Unselect the Enable button on the Inferred Dimension members and go to the next page.

Click on finish and have a look at the new output

All we need to do now is add an OLE DB Destination item where the values will be inserted.

You will need to map the values in the OLE DB Destination item 

Alright, we’re almost there. Let’s update some values on the current table.

 

update stg.Artist

set Artist = '3 Deep'

where artistid = 9

update stg.Artist

set artist = '4 Low'

where artistid = 10

Now we need to run the SSIS package we just created.

Output:

As you can see, SourceArtistID (the natural key – AristID in the source table) 9 & 10 can now be found with 2 inserts each showing start and end dates.

In addition, we also have 3 new entries with a correct timestamp by using the start parameter.