TODO

Sample Data - User Managed Staging

Purpose

Generate sample data using a SQL insert script.

Motivation

Provide sample data to run the generated code against.

Applicability

• All design metadata

Design Pattern

N/A

Schema Type

N/A

Output Type

• SQL

Implementation guidelines

This template provides a User Managed Staging sample data set for the ‘PROFILER’ system to facilitate running generating code with real data.

Considerations and consequences

N/A.

Extensions

N/A.

Code

/*
  Sample data for the User Managed Staging tables.
*/

DROP TABLE IF EXISTS [{{./conventions.landingAreaObjectDatastore}}].[{{conventions.landingAreaObjectLocation}}].[STG_USERMANAGED_SEGMENT];

CREATE TABLE [{{./conventions.landingAreaObjectDatastore}}].[{{conventions.landingAreaObjectLocation}}].[STG_USERMANAGED_SEGMENT]
(
  [INSCRIPTION_TIMESTAMP] DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
  [INSCRIPTION_RECORD_ID] INT NOT NULL IDENTITY( 1,1 ),
  [SOURCE_TIMESTAMP] DATETIME2(7) NOT NULL,
  [CHANGE_DATA_INDICATOR] CHAR(1) NOT NULL,
  [AUDIT_TRAIL_ID] INT NOT NULL,
  [CHECKSUM] BINARY(16) NOT NULL,
  [DEMOGRAPHIC_SEGMENT_CODE] NVARCHAR(MAX) NULL,
  [DEMOGRAPHIC_SEGMENT_DESCRIPTION] NVARCHAR(MAX) NULL
);

/* Create the content (for the User Managed Staging table) */
INSERT INTO [{{./conventions.landingAreaObjectDatastore}}].[{{conventions.landingAreaObjectLocation}}].[STG_USERMANAGED_SEGMENT]
(
  [SOURCE_TIMESTAMP],
  [CHANGE_DATA_INDICATOR],
  [AUDIT_TRAIL_ID],
  [CHECKSUM],
  [DEMOGRAPHIC_SEGMENT_CODE],
  [DEMOGRAPHIC_SEGMENT_DESCRIPTION]
)
VALUES
 (SYSDATETIME(), 'C', -1, (SELECT HASHBYTES('MD5', ISNULL(RTRIM(CONVERT(NVARCHAR(100),'N/A')),'NA')+'|')), CONVERT(NVARCHAR(100),'LOW'), CONVERT(NVARCHAR(100),'Lower SES')),
 (SYSDATETIME(), 'C', -1, (SELECT HASHBYTES('MD5', ISNULL(RTRIM(CONVERT(NVARCHAR(100),'N/A')),'NA')+'|')), CONVERT(NVARCHAR(100),'MED'), CONVERT(NVARCHAR(100),'Medium SES')),
 (SYSDATETIME(), 'C', -1, (SELECT HASHBYTES('MD5', ISNULL(RTRIM(CONVERT(NVARCHAR(100),'N/A')),'NA')+'|')), CONVERT(NVARCHAR(100),'HIGH'), CONVERT(NVARCHAR(100),'High SES'));