Ok, it is that time of the day where I’m starting to get high on caffeine 🙂
So this is the scenario. I have an application that is automatically generating a table in my database. It can add columns on the fly(But thankfully not delete).
It could look like this:
CREATE TABLE Cube.AppGenereatedTable ( x1value float, x2value float, ID_Region INT )
We can now query information about that table like so:
select column_name,ordinal_position,DATA_TYPE , * from information_schema.columns where table_name = 'AppGenereatedTable' AND TABLE_SCHEMA='cube'
The problem is, that right now, I do not know what to do with that data, and when I’m not at the customer anymore, more fields can be added by the application. What I would like, is a place where the customer can add more information about that column, and decide if that column should be included in some logic. To do that, I add a “metadata” table.
CREATE TABLE Dim.SomeMetadata ( ID_SomeMetadata INT, ColName nvarchar(100), friendlyName nvarchar(100), includeInETLLogig bit )
Now I want to put it all in a fact table, where I have 1 row / column value. It could look like this:
CREATE TABLE Fact.SomeFactTable ( Value float, ID_Metadata INT )
Ehhh, Ok, so know I will do what the title is saying. To load the data, I need to create the sql automaticly like so:
--Use the colnr to reference all collumns DECLARE @colnr AS INT DECLARE @sql AS NVARCHAR(MAX) SET @colnr = 1 --Do for as many collumns you expect there ever will be. Do some automation here if you like WHILE (@colnr < 50) BEGIN SET @sql = 'INSERT INTO Fact.SomeFactTable (Value, ID_Metadata) --And now we do the SQL with the join. Pay attention SELECT '+(select column_name from information_schema.columns where table_name = 'AppGenereatedTable' AND TABLE_SCHEMA='cube' AND ordinal_position=@colnr AND DATA_TYPE = 'float')+' as value ,someMetaData.ID_SomeMetadata FROM Cube.AppGenereatedTable appGenereatedTable INNER JOIN Dim.SomeMetadata someMetaData on someMetaData.ColName='''+(select column_name from information_schema.columns where table_name = 'AppGenereatedTable' AND TABLE_SCHEMA='cube' AND ordinal_position=@colnr AND DATA_TYPE = 'float')+'''' print @colnr print @sql BEGIN TRY exec( @sql ) END TRY BEGIN CATCH --Catch error. Do nothing END CATCH SET @colnr = @colnr + 1 END
Well, I don’t know if there is other than me, who can use this for something. Next post will be about precisely about what I’m using this to 🙂