Revisions Spec: Create Table

The following element, contained within a revisions spec, contains embedded Transact-SQL. The deployment utility runs the Transact-SQL to create a table to extend constituent address. When you add tables to a BBDW database, use a revision such as the one in this example. Using revisions to extend the database ensures that the extension is repeatable and easier to upgrade. Otherwise, you would have to manually execute a query to create the table in the BBDW database after every deployment.

To adapt the embedded Transact-SQL in this example for the table you want to add, adjust the table name specified in the CREATE TABLE statement as well as the attribute information. Also adjust the constraint to reflect the intended properties for your new table. For more information about the CREATE TABLE statement, see Microsoft's MSDN article at CREATE TABLE (Transact-SQL).

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.

  <DBRevision ID="10">
    <ExecuteSql>
      <![CDATA[	
create table [BBDW].[DIM_CONSTITUENTADDRESS_EXT](
  [CONSTITUENTADDRESSEXTDIMID] [int] identity(1,1) not null,	   
  [ADDRESSSYSTEMID] [uniqueidentifier] not null,
  [CONSTITUENTDIMID] [int] null,
  [CONSTITUENTSYSTEMID] [uniqueidentifier] not null,
  [ADDRESSTYPE] [nvarchar](100) null,
  [ADDRESSBLOCK] [nvarchar](150) null,  
  [CITY] [nvarchar](50) null,
  [STATE] [nvarchar](100) null,
  [STATEABBREVIATION] [nvarchar](5) null,
  [COUNTRY] [nvarchar](100) null,
  [COUNTRYABBREVIATION] [nvarchar](5) null,
  [POSTCODE] [nvarchar](12) null,  
  [CART] [nvarchar](10) null,
  [DPC] [nvarchar](8) null,
  [LOT] [nvarchar](5) null,
  [ISPRIMARY] [bit] null,   
  [ISINCLUDED] [bit] null,
  [ETLCONTROLID] [int] not null,
  [SOURCEDIMID] [int] null,
  
  constraint [PK_DIM_CONSTITUENTADDRESS_EXT] primary key clustered
  (
    [CONSTITUENTADDRESSEXTDIMID] asc
  )
) on [BBRPT_DIMGROUP];
      ]]>
    </ExecuteSql>
  </DBRevision>