Sample Stage Table
To make life easy, all audit columns in stage tables have default values. The only thing you need to change is the default value for SourceSystem.
USE ODS
DROP TABLE IF EXISTS YourSchemaName.YourStageTableNameData
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE YourSchemaName.YourStageTableNameData(
[ETLKey] [uniqueidentifier] NOT NULL,
[UniqueDims] [varbinary](35) NULL,
[UniqueRows] [varbinary](16) NULL,
[SourceSystem] [nvarchar](255) NULL,
[Cleansed] [bit] NULL,
[ErrorRecord] [bit] NULL,
[ErrorReason] [nvarchar](255) NULL,
[Processed] [bit] NULL,
[RunDate] [datetime] NULL,
CONSTRAINT [PK_YourStageTableNameData] PRIMARY KEY CLUSTERED
(
[ETLKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD CONSTRAINT [DF_YourStageTableNameData_ETLKey] DEFAULT (newid()) FOR [ETLKey]
GO
ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD CONSTRAINT [DF_YourStageTableNameData_SourceSystem] DEFAULT (N'DatabaseName') FOR [SourceSystem]
GO
ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD CONSTRAINT [DF_YourStageTableNameData_Cleansed] DEFAULT ((0)) FOR [Cleansed]
GO
ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD CONSTRAINT [DF_YourStageTableNameData_ErrorRecord] DEFAULT ((0)) FOR [ErrorRecord]
GO
ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD CONSTRAINT [DF_YourStageTableNameData_Processed] DEFAULT ((0)) FOR [Processed]
GO
ALTER TABLE [YourSchemaName].[YourStageTableNameData] ADD CONSTRAINT [DF_YourStageTableNameData_RunDate] DEFAULT (getdate()) FOR [RunDate]
GO
Last updated