Procedure frustration!

You are trying to insert more than one row into T_ADDRESS with the same primary key (address_no).

-Ryan

Can someone please look at this procedure with me. The objective is to insert a billing address into a select group of accounts. I am specifying the address. I keep getting a primary key constraint error when it tries to insert the address and I can't figure out why. I soooo appreciate it.

DECLARE @primary_address_no INT


--get St. Mikes' students that have no billing address but have the school address type and the constituency is St. Mikes

DROP TABLE #students
CREATE TABLE #students (
customer_no INT,
address_type INT,
constituency INT)

INSERT INTO #students
        ( customer_no ,
          address_type ,
          constituency
        )
SELECT a.customer_no, a.address_type, c.constituency FROM dbo.T_ADDRESS a
JOIN    tx_const_cust c ON c.customer_no = a.customer_no
WHERE a.address_type = 182
and a.customer_no NOT IN (SELECT customer_no FROM dbo.T_ADDRESS WHERE address_type = 12)
AND c.constituency = 56

/*--insert general salutation
INSERT dbo.TX_CUST_SAL
        ( signor ,
          customer_no ,
          esal1_desc ,
          lsal_desc ,
          default_ind ,
          label ,
          create_loc ,
          created_by ,
          create_dt
          
        )
SELECT  0 , -- signor - int
          s.customer_no , -- customer_no - int
          'St. Michaels College' , -- esal1_desc - varchar(55)
          'Sir or Madam' , -- lsal_desc - varchar(55)
          'N' , -- default_ind - char(1)
          'Y' , -- label - char(1)
          'gormsby' , -- create_loc - varchar(16)
          'gormsby' , -- created_by - char(8)
          '2009-12-09 15:31:56' -- create_dt - datetime
FROM #students s
s.customer_no in (select customer_no from #students)

*/


--insert St. Mike's address as the billing address
--get address_no ID
  EXECUTE @primary_address_no = ap_get_nextid_function @type = 'AD'

INSERT dbo.T_ADDRESS
        ( address_no ,
          customer_no ,
          address_type ,
          street1 ,
          street2 ,
          city ,
          state ,
          postal_code ,
          country ,
          months,
          primary_ind ,
          alt_signor ,
          label ,
          create_loc,
          created_by ,
          create_dt
          
        )
SELECT  @primary_address_no , -- address_no - int
          s.customer_no , -- customer_no - int
          12 , -- address_type - int
          'One Winooski Park SMC Box 1' , -- street1 - varchar(55)
          'St. Michaels College' , -- street2 - varchar(55)
          'Winooski' , -- city - varchar(30)
          'VT' , -- state - varchar(20)
          '05439' , -- postal_code - varchar(10)
          1 , -- country - int
          'YYYYYYYYYYYY' , -- months - char(12)
          'N' , -- primary_ind - char(1)
          0 , -- alt_signor - int
          'Y' , -- label - char(1)
          'gormsby' , -- create_loc - varchar(16)
          'gormsby' , -- created_by - char(8)
          '2009-12-09 15:15:14'  -- create_dt - datetime
 --FROM dbo.T_ADDRESS
 
 FROM #students s
 JOIN t_address a ON s.customer_no = a.customer_no
 WHERE
 s.customer_no IN (SELECT customer_no FROM #students)
 and s.customer_no NOT iN (SELECT customer_no FROM t_address WHERE address_type = 12 )
 AND s.customer_no <> 7604
 
          
        

        
--SELECT * FROM #students
--WHERE customer_no = 7604

--SELECT * FROM dbo.T_ADDRESS
--WHERE address_type = 12
--AND street1 = 'One Winooski Park SMC Box 1'

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

I’m executing the NextID function. Shouldn’t that grab a new id? Do I need to create a cursor?

David,

Thank you for your suggestions. I used the first one and it works great.

Gloria

Gloria,

You either need to put the INSERT statement into a cursor, or use a nifty new technique I just learned by poking around some Tessitura procedures, using row_number().

Basically, you use the @increment parameter of ap_get_nextid_function to reserve a block of address_no's large enough for what you are doing.  Then, using a strange syntax (see below) you add a row number to the value returned by the nextid procedure for each row you insert.  For example:

--------------------------------------------------------

<< Build a temp table of the addresses you want to add called #table>>

Declare @number_needed int, @new_address_no int

Select    @number_needed  = count(*) from #table

exec @new_address_no = [dbo].ap_get_nextid_function @type = 'AD', @increment = @number_needed

Insert into T_address (address_no, << field list here>>)

Select (row_number() over (partition by 1 order by customer_no)) + (@new_address_no-1), <<other fields here>>

-----------------------------------------------------------------------

The above assumes that you have customer_no in your temp table and that each customer gets only one new address.Yeah, the syntax is strange.

A simpler method would be to add a row number Identity column to your temp table:

Create table #temp_table( row int identity(1,1), <<other fields>>)

and just use the row field in place of the strange syntax above:

Select row+@new_address-1 as address_no, <<other stuff>>

Easier, but not as cool.

Hope this helps,

David