USP_MKTSOURCEANALYSISRULEEXCLUSION_POPULATETABLE

CREATE procedure dbo.USP_MKTSOURCEANALYSISRULEEXCLUSION_POPULATETABLE
(
  @RECORDSOURCEID uniqueidentifier,
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @SEGMENTATIONID uniqueidentifier  
)
as
  set nocount on;

  declare @MAILEXCLTABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);
  declare @SQL nvarchar(max);
  declare @RUN bit = 0;

  set @SQL = 'select @RUN = (case when exists(' + char(13) +
             '                 select *' + char(13) +
             '                 from dbo.[' + @MAILEXCLTABLENAME + ']' + char(13) +
             '                 where [DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID' + char(13) +
             '                 and [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID)' + char(13) +
             '               then 1 else 0 end)';
  exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier, @RECORDSOURCEID uniqueidentifier, @RUN bit output',  @SEGMENTATIONEXCLUSIONID =  @SEGMENTATIONEXCLUSIONID, @RECORDSOURCEID = @RECORDSOURCEID, @RUN = @RUN output;

  if @RUN = 1
    begin
      declare @SARTABLENAME nvarchar(128) = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);
      declare @SAREXCLTABLENAME nvarchar(128) = dbo.[UFN_MKTSOURCEANALYSISRULEEXCLUSION_MAKETABLENAME](@RECORDSOURCEID);
      declare @VALUESSQL nvarchar(max) = '';
      declare @INSERTCOLUMNS nvarchar(max) = '';
      declare @COLUMNNAME nvarchar(255);

      --Build the column strings...

      declare COLUMNCURSOR cursor local fast_forward for
        select [COLUMN_NAME]
        from INFORMATION_SCHEMA.COLUMNS
        where [TABLE_SCHEMA] = 'dbo'
        and [TABLE_NAME] = @SARTABLENAME
        and [COLUMN_NAME] <> 'ID';  --ID is an identity field in both SAR tables so exclude it


      open COLUMNCURSOR;
      fetch next from COLUMNCURSOR into @COLUMNNAME;

      while(@@fetch_status = 0)
      begin
        set @INSERTCOLUMNS += ', [' +@COLUMNNAME + ']';
        set @VALUESSQL += ', [SAR].[' +@COLUMNNAME + ']';

        fetch next from COLUMNCURSOR into @COLUMNNAME;
      end

      close COLUMNCURSOR;
      deallocate COLUMNCURSOR;

      --Copy data from the SAR table to the SAR exclusion table...

      set @SQL = 'insert into dbo.[' + @SAREXCLTABLENAME + '] ([SEGMENTATIONEXCLUSIONID]' + @INSERTCOLUMNS + ')' + char(13) +
                 '  select @SEGMENTATIONEXCLUSIONID' + @VALUESSQL + char(13) +
                 '  from dbo.[' + @SARTABLENAME + '] as [SAR]' + char(13) +
                 '  inner join dbo.[' + @MAILEXCLTABLENAME + '] as [MAILEXCL] on [MAILEXCL].[FINDERNUMBER] = [SAR].[FINDERNUMBER]' + char(13) +
                 '  where [MAILEXCL].[DONORQUERYVIEWCATALOGID] = @RECORDSOURCEID' + char(13) +
                 '  and [MAILEXCL].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
      exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier, @SEGMENTATIONID uniqueidentifier, @RECORDSOURCEID uniqueidentifier',  @SEGMENTATIONEXCLUSIONID =  @SEGMENTATIONEXCLUSIONID, @SEGMENTATIONID = @SEGMENTATIONID, @RECORDSOURCEID = @RECORDSOURCEID;         
    end

  return 0;