I had a project once that was using change data capture (CDC) so we could minimize the data being sent to our data warehouse that was under development.
Not always graceful, but if it works…
Being who I am, I wanted a more dynamic way to generate the statements that would query the CDC tables, and then merge that data into the data warehouse.
I wanted a CSV List of Columns
I created some basic SQL code that would go into the system tables for table and schema (since CDC was in its own schema) when given a table name and generate a CSV list I could then put in some dynamic SQL to pull the data.
This was done to reduce the complexity of the SSIS package created to collect the data. The process was designed to be table driven, so if a new table needed to be pulled into the warehouse, we just had to add the basic info into a set of master tables, and Voila, it was in the process the next time it ran.
See, dynamic code does have its place. It can make things easier, but it really depends on what it is being used for. Bulk loads to a DW once a night isn’t too bad.
I will share the notes I have on how to create that type of data warehouse load at a later time in other posts, but I wanted to get this small snippet of useful code to you sooner than that!
The code!
/** dynamic SQL to create string of column names separated by commas for use in dynamic CDC SQL for DW loading **/ declare @tablename varchar(50) set @tablename = '<CDC source tablename>' DECLARE @listStr VARCHAR(MAX) SELECT @listStr = COALESCE(@listStr+'], [' ,'') + c.Name from sys.columns C -- yeah, yeah, system tables. sometimes the views don't give the information wanted, or join as nicely as the tables themselves do. Inner Join sys.tables T on c.object_id = t.object_id Inner Join sys.schemas S on t.schema_id = s.schema_id where s.name = 'cdc' and t.name = @tablename and t.type = 'u' and c.name not like 'dw_%' order by column_id select @listStr = '[' + @listStr + ']' -- check out the list when it is done, or as it grows. Your choice! SELECT @listStr
Ok, so it isn’t very clean and well documented.
Welcome to the reality of Fast and Dirty SQL code.
This is for you to read through and see how you could use it. You could even use this to not create a list of columns dynamically from the system tables, but of other values, in your code.
Sure, you could use XML to create the list like Pinal Dave (B|T) does here, but where’s the fun in that? And it is XML! Black magic happening in there!
I admit it, I use that method too, sometimes…
If you use this, or the other method, let me know in the comments.
It is always interesting to see how people use these snippets of code and what they are up against.
You’d be surprised at how many of us face the same basic challenges, yet come up with all sorts of ways to solve them.