When building a warehouse there is a common practice of adding an Unknown row to the Dimension tables. For me, this is a monotonous task that I hate doing. I was recently on a project that had a large number of dimensions and I refused to write a T-SQL Statement for each dimension. Instead I wrote a T-SQL script that generates a T-SQL script that generates or executes the INSERT statement for me. Here is the script.
create proc dbo.UnknownRow
set @query = @query+ 'IF NOT EXISTS (SELECT * FROM ['+@TableSchema+'].['+@TableName+'] WHERE '+substring(@tablename,4,len(@tablename))+'SK= -1)'
SELECT @ColumnListing = @ColumnListing+'['+Column_Name+']'+','
set @insert = @insert+'('+ @columnlisting+')'
set @query = @query+ ' VALUES('+substring(@ValuesList,0,LEN(@valueslist))+')'
if(@Action = 'Script')
The stored procedure in the above has one required parameter the TableName and two optional parameters SchemaName and Action. The TableName and SchemaName are obvious. If you accept the default for Action it generates the script, but if you pass anything else it will execute the generated script. Either way, the generated script is an insert statement that adds the unknown row to the specified dimension. This script has been worked on by a few of the guys on our team. We have not exhausted all of the possible SQL Server data types. If you modify the script please share it with us.
Talk to you soon,
Visit www.BIDN.com, Bring Business Intelligence to your company.