USP_EVENTPREFERENCE_COPY

            CREATE procedure dbo.USP_EVENTPREFERENCE_COPY
            (
                @SOURCEEVENTID uniqueidentifier,
                @DESTINATIONEVENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            with execute as caller
            as
                set nocount on;

                -- Cannot copy if the source event does not exist

                if not exists (select ID from dbo.EVENT where ID = @SOURCEEVENTID)
                    raiserror('The source event specified does not exist.',13,1);

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

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

                declare @PREFERENCEGROUPMAP table
                (
                    SOURCEID uniqueidentifier,
                    DESTINATIONID uniqueidentifier
                )

                insert into @PREFERENCEGROUPMAP
                (
                    SOURCEID,
                    DESTINATIONID
                )
                select
                    ID,
                    coalesce
                    (
                        (
                            select
                                ID
                            from
                                dbo.EVENTPREFERENCEGROUP INNEREVENTPREFERENCEGROUP
                            where
                                INNEREVENTPREFERENCEGROUP.EVENTID = @DESTINATIONEVENTID
                                and INNEREVENTPREFERENCEGROUP.[NAME] = EVENTPREFERENCEGROUP.[NAME]
                        ),
                        newid()
                    )
                from
                    dbo.EVENTPREFERENCEGROUP
                where
                    EVENTID = @SOURCEEVENTID;


                insert into dbo.EVENTPREFERENCEGROUP
                (
                    ID,
                    EVENTID,
                    [NAME],
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    MAP.DESTINATIONID,
                    @DESTINATIONEVENTID,
                    EVENTPREFERENCEGROUP.[NAME],
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    @PREFERENCEGROUPMAP MAP
                    left join dbo.EVENTPREFERENCEGROUP on MAP.SOURCEID = EVENTPREFERENCEGROUP.ID
                where
                    not exists
                    (
                        select
                            ID
                        from
                            dbo.EVENTPREFERENCEGROUP INNEREVENTPREFERENCEGROUP
                        where
                            INNEREVENTPREFERENCEGROUP.ID = MAP.DESTINATIONID
                    )
                order by
                    EVENTPREFERENCEGROUP.[NAME];


                insert into dbo.EVENTPREFERENCE
                    (
                        EVENTPREFERENCEGROUPID,
                        [NAME],
                        SEQUENCE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED
                    )
                select
                    MAP.DESTINATIONID,
                    EVENTPREFERENCE.[NAME],
                    (
                        select
                            coalesce(max(INNEREVENTPREFERENCE.SEQUENCE), 0) + EVENTPREFERENCE.SEQUENCE + 1
                        from
                            dbo.EVENTPREFERENCE INNEREVENTPREFERENCE
                        where
                            INNEREVENTPREFERENCE.EVENTPREFERENCEGROUPID = MAP.DESTINATIONID
                    ),
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    @PREFERENCEGROUPMAP MAP
                    inner join dbo.EVENTPREFERENCE on MAP.SOURCEID = EVENTPREFERENCE.EVENTPREFERENCEGROUPID
                where
                    not exists
                    (
                        select
                            ID
                        from
                            dbo.EVENTPREFERENCE INNEREVENTPREFERENCE
                        where
                            INNEREVENTPREFERENCE.EVENTPREFERENCEGROUPID = MAP.DESTINATIONID
                            and INNEREVENTPREFERENCE.[NAME] = EVENTPREFERENCE.[NAME]
                    )
                order by
                    EVENTPREFERENCE.SEQUENCE;

                return 0;