Insert/Update the evex_no in TX_EVENT_EXTRACT

Hi all,

Last week I inserted 208 rows into TX_EVENT_EXTRACT and did the following to get around the evex_no (code below). However, being the SQL newbie I am…..didn’t realize that this would break the evex_no for any further invitations added in the normal way. As a temporary solution, I have updated my 208 inserts so that their evex_no are +1000 so duplicates won’t come up for a while.

Does anyone know how/where I can insert my evex_no’s so they are recognized and recorded correctly in the system?

Also, how do others get around doing Insert Select statements with a non-identity column as a PK? I searched and search online but couldn’t find anything. I hope I have made sense….

Thanks as always,

Tash

Declare

            @maxid int

Set   @maxid = (Select MAX(evex_no)+1 from TX_EVENT_EXTRACT)

      While exists (Select * from #invitations)

Begin

      If @maxid in (select evex_no from TX_EVENT_EXTRACT) continue;

Insert into TX_EVENT_EXTRACT

Select top 1

                  @maxid,

                  a.campaign_no,

                  a.customer_no,

                  a.source_no,

                  a.inv_status,

                  a.reserved_ind,

                  a.recd_amt,

                  a.num_attendees,

                  a.notes,

                  a.mir_lock

      from  #invitations a

Set @maxid = @maxid+1

delete from #invitations where customer_no in (Select MIN(customer_no)from #invitations)

End