Revisions Spec Exercise: Create Table View Steps

1. Open the revisions file to edit.
2. Add a section to create a table view. For more information, see the example at Revisions Spec: Create Table View

3. Use "50" to number the ID for that revision.

   <DBRevision ID="10">
   </DBRevision>

4. Within <DBRevision ID="50">, add element to embed Transact-SQL code.

       <ExecuteSql>
         <![CDATA[
       ] ]>
       </ExecuteSql>

   For more information about CDATA sections, see Microsoft's MSDN article at CDATA Sections [XML Standards].

5. Within that, add Transact-SQL code to create a view.

   create view [BBDW].[v_FACT_INTERACTONRESPONSE_EXT] as
     select
   	  [INTERACTIONRESPONSEFACTID],
   	  [INTERACTIONRESPONSESYSTEMID],
   	  [INTERACTIONFACTID],
   	  [RESPONSEDIMID],
   	  [INTERACTIONRESPONSEDATEDIMID],
   	  [INTERACTIONRESPONSEDATE],
   	  [ISINCLUDED],
   	  [ETLCONTROLID],
   	  [SOURCEDIMID]
     from BBDW.[FACT_INTERACTONRESPONSE_EXT]

   To maintain consistent naming that avoids conflicts with out-of-the-box tables, apply the _EXT suffix to names for extension tables. If the extension is for a staging table, also apply the _STAGE suffix. Always apply _EXT first. Appropriate suffixes are _EXT and _EXT_STAGE.

   To maintain consistent naming for views, use the v_ prefix.

   [BBDW] is the database schema.

   For more information about the CREATE VIEW statement, see Microsoft's MSDN article at CREATE VIEW (Transact-SQL).

6. Save your revisions file.

   The revision should look like this:

     <DBRevision ID="50">
       <ExecuteSql>
         <![CDATA[
   create view [BBDW].[v_FACT_INTERACTONRESPONSE_EXT] as
     select
   	  [INTERACTIONRESPONSEFACTID],
   	  [INTERACTIONRESPONSESYSTEMID],
   	  [INTERACTIONFACTID],
   	  [RESPONSEDIMID],
   	  [INTERACTIONRESPONSEDATEDIMID],
   	  [INTERACTIONRESPONSEDATE],
   	  [ISINCLUDED],
   	  [ETLCONTROLID],
   	  [SOURCEDIMID]
     from BBDW.[FACT_INTERACTONRESPONSE_EXT]
         ] ]>
       </ExecuteSql>
     </DBRevision>