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;