USP_GLOBALCHANGE_BUILDCONSTITUENTETI

            CREATE procedure dbo.USP_GLOBALCHANGE_BUILDCONSTITUENTETI
            (
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output
            )
            as            
                set nocount off;

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                -- Delete all records from the ETI tables

                truncate table dbo.NAMEETI;
                truncate table dbo.ADDRESSETI;
                truncate table dbo.PHONEETI;
                truncate table dbo.EMAILADDRESSETI;

                -- Disable all the non-clustered indexes

                alter index [IX_NAMEETI_CONSTITUENTID] ON [dbo].[NAMEETI] disable;
                alter index [IX_ADDRESSETI_CONSTITUENTID] ON [dbo].[ADDRESSETI] disable;
                alter index [IX_ADDRESSETI_ADDRESSID] ON [dbo].[ADDRESSETI] disable;
                alter index [IX_PHONEETI_CONSTITUENTID] ON [dbo].PHONEETI disable;
                alter index [IX_PHONEETI_PHONEID] ON [dbo].PHONEETI disable;
                alter index [IX_EMAILADDRESSETI_CONSTITUENTID] ON [dbo].EMAILADDRESSETI disable;
                alter index [IX_EMAILADDRESSETI_EMAILADDRESSID] ON [dbo].EMAILADDRESSETI disable;

                declare @COUNT int;
                set @COUNT = 0;

                --NAMEETI

                with NAMES_CTE as
                (
                    select 
                        c.ID, 
                        c.NAME + ' ' + coalesce(s.DESCRIPTION, '') NAME
                    from dbo.CONSTITUENT c
                    left outer join dbo.SUFFIXCODE s
                    on c.SUFFIXCODEID = s.ID
                )
                insert into dbo.NAMEETI(CONSTITUENTID, TOKEN)
                select ID, TOKEN
                from NAMES_CTE
                cross apply dbo.UFN_PARTITIONSTRING_PARTITIONINTOWORDS(NAME)
                set @COUNT = @COUNT + @@ROWCOUNT;

                -- ADDRESS ETI

                with ADDRESS_CTE as
                (
                    select 
                        a.ID,
                        a.CONSTITUENTID, 
                        coalesce(a.ADDRESSBLOCK, '') + ' ' + coalesce(a.CITY, '') +' ' + coalesce(s.ABBREVIATION, '') + ' ' + coalesce(a.POSTCODE,'') ADDRESS
                    from dbo.ADDRESS a
                    left outer join dbo.STATE s
                    on a.STATEID = s.ID
                )
                insert into dbo.ADDRESSETI(ADDRESSID, CONSTITUENTID, TOKEN)
                select ID, CONSTITUENTID, TOKEN
                from ADDRESS_CTE
                cross apply UFN_PARTITIONSTRING_PARTITIONINTOWORDS(ADDRESS)
                set @COUNT = @COUNT + @@ROWCOUNT;

                --PHONETI

                with PHONE_CTE as
                (
                    select 
                        ID,
                        CONSTITUENTID, 
                        NUMBER
                    from dbo.PHONE
                )
                insert into dbo.PHONEETI(PHONEID, CONSTITUENTID, TOKEN)
                select ID, CONSTITUENTID, TOKEN
                from PHONE_CTE
                cross apply UFN_PARTITIONSTRING_PARTITIONINTOWORDS(NUMBER)
                set @COUNT = @COUNT + @@ROWCOUNT;

                --EMAILETI

                with EMAIL_CTE as
                (
                    select 
                        ID,
                        CONSTITUENTID, 
                        EMAILADDRESS
                    from dbo.EMAILADDRESS
                )
                insert into dbo.EMAILADDRESSETI(EMAILADDRESSID, CONSTITUENTID, TOKEN)
                select ID, CONSTITUENTID, TOKEN
                from EMAIL_CTE
                cross apply UFN_PARTITIONSTRING_PARTITIONINTOWORDS(EMAILADDRESS)
                set @COUNT = @COUNT + @@ROWCOUNT;

                set @NUMBERADDED = @COUNT;
                set @NUMBEREDITED = 0;
                set @NUMBERDELETED = 0;

                -- Rebuild the non-clustered indexes after the ETI tables have been populated

                alter index [IX_EMAILADDRESSETI_CONSTITUENTID] ON [dbo].EMAILADDRESSETI rebuild;
                alter index [IX_EMAILADDRESSETI_EMAILADDRESSID] ON [dbo].EMAILADDRESSETI rebuild;
                alter index [IX_NAMEETI_CONSTITUENTID] ON [dbo].[NAMEETI] rebuild;
                alter index [IX_ADDRESSETI_CONSTITUENTID] ON [dbo].[ADDRESSETI] rebuild;
                alter index [IX_ADDRESSETI_ADDRESSID] ON [dbo].[ADDRESSETI] rebuild;
                alter index [IX_PHONEETI_CONSTITUENTID] ON [dbo].PHONEETI rebuild;
                alter index [IX_PHONEETI_PHONEID] ON [dbo].PHONEETI rebuild;