Landing Area SQL Server Table

Landing Area Generate Table

Purpose

This code-generation template creates table creation scripts for SQL Server based on Landing Area conventions.

Motivation

After importing data definitions from operational (‘source’) systems, a typical next step is generating corresponding Landing Area objects to load the data delta into. This template creates these tables, using the operational system data object definition as input.

Applicability

• SQL Server family databases

Design Pattern

• Staging / Landing

Schema Type

• Data Object

Output Type

• Data Definition Language (DDL)

Implementation guidelines

This template is intended to be assigned to ‘source’ Data Objects. It will add the defined data solution standard columns and streamline the data types.

Considerations and consequences

Note

Character columns that are part of the Primary Key definition will be generated with character length 100. This is because these columns are used in various window functions and indexes, which are subject to size / length limitations in SQL Server.

Extensions

N/A.

Code

--
-- Landing Area table creation statement for [{{./conventions.landingAreaObjectDatastore}}].[{{./conventions.landingAreaObjectLocation}}].[{{./conventions.landingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}].
--
-- Generated from template '{{templateMetadata.name}}'.
--

DROP TABLE IF EXISTS [{{./conventions.landingAreaObjectDatastore}}].[{{./conventions.landingAreaObjectLocation}}].[{{./conventions.landingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}];

CREATE TABLE [{{./conventions.landingAreaObjectDatastore}}].[{{./conventions.landingAreaObjectLocation}}].[{{./conventions.landingAreaObjectPrefix}}_{{lookupExtension extensions "originatingSystem"}}_{{name}}]
(
    [{{./conventions.inscriptionTimeStampColumn}}] [DATETIME2](7) NOT NULL DEFAULT (SYSDATETIME())
   ,[{{./conventions.inscriptionRecordIdColumn}}] INT IDENTITY(1,1) NOT NULL
   ,[{{./conventions.sourceTimestampColumn}}] [DATETIME2](7) NOT NULL
   ,[{{./conventions.changeDataColumn}}] [CHAR](1) NOT NULL
   ,[{{./conventions.auditTrailIdColumn}}] [INT] NOT NULL
   ,[{{./conventions.checksumColumn}}] BINARY(16) NOT NULL
{{#each dataItems}}
   ,[{{~stringupper name~}}] {{!-- Add any type conversions below --}}
   {{~#stringcompare dataType "int"}}INT{{else}}
   {{~#stringcompare dataType "date"}}DATETIME2(7){{else}}
   {{~#stringcompare dataType "datetime"}}DATETIME2(7){{else}}
   {{~#stringcompare dataType "decimal"}}NUMERIC(38,20){{else}}
   {{~#stringcompare dataType "number"}}NUMERIC(38,20){{else}}
   {{~#stringcompare dataType "numeric"}}NUMERIC(38,20){{else}}
   {{~#stringcompare dataType "varchar"}}NVARCHAR({{#hasStringValue ../../../../../../../../dataItemsMetadata.keyDataItems name}}100{{else}}MAX{{/hasStringValue}}){{else}}
   {{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}}{{/stringcompare}} NULL
{{/each}}
);