Add a Column to a View That Uses Batch Job or Slowly Changing Dimensions (Simple Version)

The example first shows how to manually insert a default value and then gives the command to read the values via a subquery.

There are a few things to keep in mind:

• The snippets assume that field "Status" of type "short" shall be inserted;
• The sample uses the AdventureWorks database;
• This example supposes that the view is being replicated into the table "dwh.foo".

Please be thorough and careful when implementing such operations!

CREATE VIEW "views.aw" AS
SELECT
  "salesorderheader.SalesOrderID",
  "salesorderheader.CustomerID",
  "salesorderheader.SalesPersonID"
FROM
  "adv.salesorderheader"

This creates optimization and batch job that replicates to dwh.foo.

Now the column can be added. We create a dummy table (dwh.bar) with a SELECT INTO that adds the new column and automatically sets a default value for all rows. Then the original table is dropped and recreated from the dummy table again using SELECT INTO:

SELECT a.*, CAST(5 as short ) as "Status" INTO "dwh.bar" 
FROM "dwh.foo" as a;; 
DROP TABLE "dwh.foo";; 
SELECT a.* INTO "dwh.foo" FROM "dwh.bar" as a;;

The dummy table can be dropped and the view can be altered. The ALTER VIEW command can be executed at first. This guide performs this action as the last one:

DROP TABLE "dwh.bar";;
ALTER VIEW "views.aw"
AS
SELECT
  "salesorderheader.SalesOrderID",
  "salesorderheader.CustomerID",
  "salesorderheader.SalesPersonID",
  "salesorderheader.Status"
FROM
  "adv.salesorderheader";; 

Alternativele, you can execute the following query:

SELECT a.*, <SUBQUERY> as "Status" INTO "dwh.bar" FROM "dwh.foo" as a;;

Example:

SELECT a.*, CAST((SELECT "b.Status" FROM "adv.salesorderheader" as b 
WHERE b.SalesOrderID = a.SalesOrderID) as short) as "Status" 
INTO "dwh.bar" FROM "dwh.foo" as a;;