How to do an equivalent VB record set in SQL 7.0

[sandra64]

Dear all

I am trying to insert into a table (0 to whatever number of records). One of the fields is incremented by one for each record. (It looks at another table and just takes the number and adds one to it). because my insert does them all in one go all records have the same number on them.

I need to be able to do the equivalant to a record set in VB so that I can insert each one at a time and then increment the table with the number on it ready for the next insert.

INSERT INTO payment(site_id,
income_date,
total_amount,
bank_date,
slip_no,
status,
payment_type,
rec_date,
fms_date,
site_pay_no,
amended)
SELECT p.site_id,
b.bank_date,
b.i_amount,
b.bank_date,
b.reference_numb,
'R',
'P',
convert(varchar,getdate(),103),
null,
s.next_pay_no + 1,
'N'
FROM bank b ,
payin_book p,
site s
WHERE reference_numb >= range_from
AND reference_numb <= range_to
AND p.site_id = s.site_id
AND b.status = 'R'

UPDATE site
SET next_pay_no = next_pay_no + 1
FROM site s, payment p
WHERE p.site_id = s.site_id
AND status = 'R'

 

[JohnDTampaBay]

From the looks of things, the site_pay_no has to be sequential by site. One solution is to wrap your code in a cursor. One row at a time, insert into payment then update site with the new next_pay_no. But I don't like cursors, so here's another idea. Good luck!

SELECT  p.site_id,
        b.bank_date,
        b.i_amount,
        b.bank_date,
        b.reference_numb,
        'R' AS status,
        'P' AS payment_type,
        convert(varchar,getdate(),103) AS rec_date,
        null AS fms_date,
        Identity(int,1,1) AS ID,
        'N' AS Amended,
        p.site
INTO #tmp
FROM    bank b , 
        payin_book p
WHERE   reference_numb >= range_from
AND     reference_numb <= range_to
AND     b.status = 'R'

INSERT INTO payment(site_id,
            income_date,
            total_amount,
            bank_date,
            slip_no,
            status,
            payment_type,
            rec_date,
            fms_date,
            site_pay_no,
            amended)
SELECT      t.site_id,
            t.bank_date,
            t.i_amount,
            t.bank_date,
            t.reference_numb,
            t.status,
            t.payment_type,
            t.rec_date,
            t.fms_date,
            s.next_pay_no + t.ID,
            t.amended
FROM #tmp t, site s
WHERE t.site = p.site

UPDATE site
SET next_pay_no = next_pay_no + (SELECT Max(ID) FROM #tmp)
FROM site s, payment p
WHERE p.site_id = s.site_id
AND status = 'R'[code]

--Angel [rainbow]
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sandra64]

Angel,

Thanks for that, nearly there. This works if it was the same site but the sites could differ and their site_pay_no's differ. I will fiddle with it and see what I can do, thanks for getting me on the right track

cheers
Sandra

 

[JohnDTampaBay]

Oops! A small change to the UPDATE portion should do the trick. How does this work:

UPDATE site
SET next_pay_no = next_pay_no + (SELECT Max(ID) FROM #tmp t
                                 WHERE t.site_id = p.site_id)
FROM site s, payment p
WHERE p.site_id = s.site_id
AND status = 'R'

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sandra64]

Angel

Fantastic!! that did the trick. I also managed to do it using CURSORs so now I have two ways... which one shall I choose

thanks for all your help much appreciated

 

[JohnDTampaBay]

A wise guru once told me, &quot;Set-based is the true path, Grasshopper.&quot;

CURSORs are resource hogs and should be avoided unless there is just no set-based solution. You might not see much of a performance difference if you're dealing with a few rows, but when dealing with thousands of rows the difference is significant.

Just my two cents. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.