USP_MKTSEGMENTATIONDATAEXCLUSION_POPULATETABLE

CREATE procedure dbo.[USP_MKTSEGMENTATIONDATAEXCLUSION_POPULATETABLE]
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @UPDATEMAILINGCOUNTSPROCESSID uniqueidentifier,
  @IMPORTTABLENAME nvarchar(128)
)
as
  set nocount on;

  declare @DATATABLENAME nvarchar(128);
  declare @EXCLUSIONTABLENAME nvarchar(128);

  select
    @DATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([SEGMENTATIONID]),
    @EXCLUSIONTABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([SEGMENTATIONID])
  from dbo.[MKTUPDATEMAILINGCOUNTSPROCESS]
  where [ID] = @UPDATEMAILINGCOUNTSPROCESSID;


  declare @SQL nvarchar(max);
  declare @COLUMNSQL nvarchar(max) = '[SEGMENTATIONEXCLUSIONID]';
  declare @VALUESSQL nvarchar(max) = '@SEGMENTATIONEXCLUSIONID';
  declare @COLUMNNAME nvarchar(128);
  declare @COLUMNCURSOR cursor;  

  set @SQL = 'set @COLUMNCURSOR = cursor local fast_forward for' + char(13) +
             '  select [COLUMN_NAME]' + char(13) +
             '  from INFORMATION_SCHEMA.COLUMNS' + char(13) +
             '  where ([TABLE_SCHEMA] = ''dbo'') and ([TABLE_NAME] = ''' + @DATATABLENAME + ''');' + char(13) +
             'open @COLUMNCURSOR;';
  exec sp_executesql @SQL, N'@COLUMNCURSOR cursor output', @COLUMNCURSOR = @COLUMNCURSOR output;
  fetch next from @COLUMNCURSOR into @COLUMNNAME;

  while @@FETCH_STATUS = 0
  begin
      set @COLUMNSQL += ', [' + @COLUMNNAME + ']';
      set @VALUESSQL += ', [DATA].[' + @COLUMNNAME + ']';
      fetch next from @COLUMNCURSOR into @COLUMNNAME;
  end

  close @COLUMNCURSOR;
  deallocate @COLUMNCURSOR;


  set @SQL = 'insert into dbo.[' + @EXCLUSIONTABLENAME + '] (' + @COLUMNSQL + ')' + char(13) +
             '  select ' + @VALUESSQL + char(13) +
             '  from dbo.[' + @DATATABLENAME + '] as [DATA]' + char(13) +
             '  inner join dbo.[' + @IMPORTTABLENAME + '] as [IMPORT] on [DATA].[FINDERNUMBER] = [IMPORT].[FINDERNUMBER]' + char(13) +
             '  where [IMPORT].[EXCEPTIONCODE] = 0;';
  exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier',  @SEGMENTATIONEXCLUSIONID =  @SEGMENTATIONEXCLUSIONID;         

  return 0;