USP_MKTRECORDSOURCE_CREATEMATCHBACKTABLE

CREATE procedure dbo.[USP_MKTRECORDSOURCE_CREATEMATCHBACKTABLE]
(
  @RECORDSOURCEID uniqueidentifier,
  @GIFTQUERYVIEWCATALOGID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @MATCHBACKTABLE nvarchar(128);
  declare @DONORIDDATATYPE nvarchar(50);
  declare @GIFTIDDATATYPE nvarchar(50);
  declare @SQL nvarchar(max);
  declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

  begin try
    select
      @DONORIDDATATYPE = [PRIMARYKEYTYPENAME],
      @MATCHBACKTABLE = dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME]([ID])
    from dbo.[QUERYVIEWCATALOG]
    where [ID] = @RECORDSOURCEID;

    select
      @GIFTIDDATATYPE = [PRIMARYKEYTYPENAME]
    from dbo.[QUERYVIEWCATALOG]
    where [ID] = @GIFTQUERYVIEWCATALOGID;

    -- putting this in to allow alternate record sources to be turned on and off more easily while testing

    -- will most likely never come into play for clients

    if exists (select top 1 1 from sys.objects where name = @MATCHBACKTABLE and type = 'U')
      begin
        set @SQL = 'drop table dbo.[' + @MATCHBACKTABLE + '];';

        exec (@SQL);
      end

    set @SQL = 'create table dbo.[' + @MATCHBACKTABLE + '] (' + char(13) +
               '  [ID] uniqueidentifier rowguidcol not null,' + char(13) +
               '  [DONORID] ' + @DONORIDDATATYPE + ' not null,' + char(13) +
               '  [GIFTID] ' + @GIFTIDDATATYPE + ' not null,' + char(13) +
               '  [SEGMENTATIONID] uniqueidentifier not null' + char(13) +
               '  constraint [PK_' + @MATCHBACKTABLE + '] primary key clustered ([GIFTID] asc) with (IGNORE_DUP_KEY = OFF)' + char(13) +
               ')';

    if @USECOMPRESSION = 1
      set @SQL = @SQL + ' WITH (DATA_COMPRESSION = PAGE)';

    exec (@SQL);

    --Do not create a foreign key on ID to MKTSEGMENTLISTDATA, so we don't have to insert vendor managed IDs into two tables.

    --The foreign key would force us to insert mostly blank rows into the imported list data table, which we don't want to do.


    set @SQL = 'alter table dbo.[' + @MATCHBACKTABLE + '] with check add constraint [FK_' + @MATCHBACKTABLE + '_SEGMENTATIONID] foreign key([SEGMENTATIONID]) references dbo.[MKTSEGMENTATION] ([ID]) on delete cascade';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @MATCHBACKTABLE + '_ID] on dbo.[' + @MATCHBACKTABLE + '] ([ID] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @MATCHBACKTABLE + '_DONORID] on dbo.[' + @MATCHBACKTABLE + '] ([DONORID] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @MATCHBACKTABLE + '_SEGMENTATIONID] on dbo.[' + @MATCHBACKTABLE + '] ([SEGMENTATIONID] asc) include ([DONORID], [ID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]';
    exec (@SQL);

    set @SQL = 'grant select, insert, update, delete on dbo.[' + @MATCHBACKTABLE + '] to [BBAPPFXSERVICEROLE]';
    exec (@SQL);
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;