Dynamic T-SQL for serialization of columns in a table

Sometimes you might need in your ETL project to move data between stages with explicit column name definition in the queries. I typically end up using the following code snippet I’ve written for straight moves between stages. You could do this without dynamic SQL as well, but I prefer this approach as I usually use this snippet in a iteration to reload all tables based on evaluating some configuration logic inside a config table. The dynamic SQL also helps to append to the columns list stage-specific values, replacing values,dynamically evaluated lookups etc. As you can guess from my previous posts, the next step is creating the INSERT INTO (@COLUMN_LIST) SELECT @COLUMN_LIST statements and kicking this off with a declared batch size as a variable. Simple stuff but having explicitly defined column names is often a must. Next time I’d like to blog on AWS Batch and event-driven data processing so stay tuned.


USE [AdventureWorks2014];
GO

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

DECLARE @STMT_MOVE_PRE_STG_COLS nvarchar(max);
DECLARE @COLUMN_LIST nvarchar(max);
DECLARE @schema_name nvarchar(255);
DECLARE @table_name nvarchar(255);
SET @schema_name = 'Production'
SET @table_name = 'Product'

SET @STMT_MOVE_PRE_STG_COLS = 'SELECT @COLUMN_LIST_IN =
STUFF(
(SELECT '','' + C.NAME
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES T ON C.Object_ID = T.Object_ID
INNER JOIN SYS.SCHEMAS S ON S.Schema_ID = T.Schema_ID
WHERE S.NAME = '''+ @schema_name +''' AND T.NAME = '''+ @table_name + '''
ORDER BY C.NAME
FOR XML PATH ('''')), 1, 1, '''')';

EXECUTE SP_EXECUTESQL @STMT_MOVE_PRE_STG_COLS, N'@COLUMN_LIST_IN NVARCHAR(MAX) OUT', @COLUMN_LIST out;

PRINT @COLUMN_LIST;
Advertisements