Generating SELECT Statements Using GetColumnList

Why This Pattern Matters

Once a Biml project can generate SQL from metadata, the next step is shaping the column lists that feed into SELECT, INSERT, JOIN, and MERGE statements. The 'GetColumnList' method on a table node returns a comma separated list of columns ready to drop into a T-SQL statement, and a few optional parameters cover the cases where the default output is not enough.

What GetColumnList Returns

By default, 'GetColumnList' returns every column on a table, square bracket delimited and comma separated:

[CustomerID], [FirstName], [LastName]

That string can be customized by passing in a table alias, a predicate to filter the columns, or replacement delimiters. With those parameters, the same call can produce something like this instead:

c.FirstName, c.LastName

The sections below show the six common variations.

Generating a SELECT for Every Source Table

A connection node and an import call set up the metadata, and a foreach loop emits one SELECT per table:

<#
  var srcConn = SchemaManager.CreateConnectionNode("Sales", @"Data Source=...;");
  var srcSchema = srcConn.GetDatabaseSchema();
#>

<# foreach (var srcTable in srcSchema.TableNodes) { #>
  SELECT <#=srcTable.GetColumnList()#>
  FROM <#=srcTable.SchemaQualifiedName#>;
<# } #>

The output is a series of statements that look like this:

SELECT [RegionID], [RegionName], [CountryID], [PostalCode] ...
FROM [sales].[Regions];

SELECT [CountryID], [CountryName], [IsoCode], [Currency] ...
FROM [sales].[Countries];

The result is a plain text script. It can be pasted into a query editor for ad hoc use, or wrapped in further Biml to drive an OleDbSource transformation inside an SSIS package.

Customizing the Output With Parameters

'GetColumnList' accepts up to four parameters. Three are simple strings: 'tableAlias', 'beginningDelimiter', and 'endingDelimiter'. The fourth is a 'predicate', supplied as a lambda expression. The available combinations are:

Method	Example Output
GetColumnList()	[CustomerID], [FirstName], [LastName]
GetColumnList("c")	[c].[CustomerID], [c].[FirstName], [c].[LastName]
GetColumnList(col => col.IsUsedInPrimaryKey)	[CustomerID]
GetColumnList(col => col.IsUsedInPrimaryKey, "c")	[c].[CustomerID]
GetColumnList("c", "", "")	c.CustomerID, c.FirstName, c.LastName
GetColumnList(col => col.IsUsedInPrimaryKey, "c", "", "")	c.CustomerID

What the Predicate Parameter Does

The predicate is a C# lambda expression. The arrow '=>' separates the input parameter on the left from the boolean expression on the right:

column => column.Name == "CustomerID"

When 'GetColumnList' is called with a predicate, every column on the table is evaluated against the expression. Columns that return true are kept, columns that return false are dropped. The input parameter can be named anything, so a shorter form is common:

col => col.Name == "CustomerID"

The predicate can inspect any property exposed by a column node, which means filters can be expressed against name, data type, nullability, or key membership:

col => col.Name == "CustomerID"
col => col.DataType != System.Data.DbType.Guid
col => col.IsNullable
col => !col.IsUsedInKey

Example: SELECT With a Table Alias

Passing the alias parameter prefixes every column with the alias, and the same alias is used in the FROM clause:

SELECT <#=srcTable.GetColumnList("src")#>
FROM <#=srcTable.SchemaQualifiedName#> AS src;

The output looks like this:

SELECT [src].[RegionID], [src].[RegionName], [src].[CountryID], [src].[PostalCode] ...
FROM [sales].[Regions] AS src;

Example: SELECT Only Specific Columns

Passing a predicate restricts the output to columns that match. The example below returns only the primary key columns:

SELECT <#=srcTable.GetColumnList(col => col.IsUsedInPrimaryKey)#>
FROM <#=srcTable.SchemaQualifiedName#>;

The output looks like this:

SELECT [RegionID]
FROM [sales].[Regions];

If a table does not have a primary key, the column list will be empty for that table.

Example: Predicate, Alias, and Custom Delimiters

The fullest form of the call combines a predicate, an alias, and a pair of replacement delimiters. Passing two empty strings as the delimiters strips the square brackets:

SELECT <#=srcTable.GetColumnList(col => !col.IsUsedInPrimaryKey, "src", "", "")#>
FROM <#=srcTable.SchemaQualifiedName#>;

The output looks like this:

SELECT src.RegionName, src.CountryID, src.PostalCode ...
FROM sales.Regions AS src;

Summary

'GetColumnList' produces customizable column lists from table metadata. The four parameters cover aliasing, filtering by predicate, and overriding the default square bracket delimiters, which is enough to assemble most repetitive T-SQL patterns from the same source metadata that drives package generation.