Urgent Cursor Assitance Required
-
Hi,
Could you provide some advice on a cursor query?
Selecting data from one server to put into a database on a different.
should select 70k odd patients,
logic: check to see if the debtor exists, if not, inserts it, then inserts that patient.
problem is, the FETCH_STATUS is returning -2 when more than one patient is returned to the cursor...
when READ_ONLY is not used, the error "Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor."
But I am not updating the cursor
query below
DECLARE PATIENT_CURSOR CURSOR FOR
-- SELECT PATIENTS THAT ARE NOT IN TANGO.PLATYPUS2
SELECT
P1.SB_PATIENT_MRN,
P1.SB_HL7_SOURCE,
P1.SB_PATIENT_MEDICARE_NUMBER,
P1.SB_CURRENT_HEALTH_FUND_NO,
P1.SB_PATIENT_SURNAME,
P1.SB_PATIENT_OTHER_NAMES,
P1.SB_PATIENT_DOB,
P1.SB_PATIENT_TITLE,
P1.SB_PATIENT_ALIVE,
P1.SB_PATIENT_CREATE_DATE,
P1.SB_PATIENT_SEX,
P1.SB_HEALTH_FUND_CODE,
P1.SB_REDIRECTED_TO,
P1.SB_MEDICARE_NO_REF,
P1.SB_PATIENT_SURNAME_FUND,
P1.SB_PATIENT_UPI,
P1.SB_DVA_NUMBER,
P1.SB_PATIENT_OTHER_NAMES_FUND,
P1.SB_FUND_CARD_REF_NO,
P1.SB_POST_MERGE_MRN,
P1.SB_MERGE_DATE,
P1.SB_PAT_ADDRESS_1,
P1.SB_PAT_ADDRESS_2,
P1.SB_PAT_SUBURB,
P1.SB_PAT_STATE,
P1.SB_PAT_POST_CODE,
P1.SB_PAT_PHONE_1,
P1.SB_PAT_PHONE_2,
P1.SB_PENSION_NUMBER,
P1.SB_SAFETY_NET_NUMBER,
P1.SB_SAFETY_NET_EXPIRY,
P1.SB_DIALYSIS_PATIENT,
P1.SB_PAT_COMMENT,
P1.SB_HIC_ALT_FIRST_NAME,
P1.SB_HIC_ALT_SURNAME,
P1.SB_HIC_ALT_SEX,
P1.SB_DVA_CARD_COLOUR,
P1.SB_PATIENT_COUNTRY,
P1.SB_PREV_MEDICARE_NO,
P1.SB_PREV_MEDICARE_REF,
P1.SB_PREV_DVA_NO,
P1.SB_PENSIONER,
P1.SB_DVA,
D1.SB_DEBTOR_COMPANY_NAME,
D1.SB_DEBTOR_ADDRESS_1,
D1.SB_DEBTOR_ADDRESS_2,
D1.SB_DEBTOR_SUBURB,
D1.SB_DEBTOR_STATE,
D1.SB_DEBTOR_POSTCODE,
D1.SB_DEBTOR_CONTACT_SURNAME,
D1.SB_DEBTOR_CONTACT_GIVEN_NAME,
D1.SB_DEBTOR_CONTACT_TITLE,
D1.SB_DEBTOR_PHONE_1,
D1.SB_DEBTOR_PHONE_2,
D1.SB_DEBTOR_EMAIL,
D1.SB_STANDARD_DEBTOR,
D1.SB_GST_MAY_APPLY,
D1.SB_DEBTOR_CODE,
D1.SB_CURRENT,
D1.SB_DEBTOR_COUNTRY,
D1.SB_DEBTOR_MOBILE
FROM
RISBILL.PLATYPUS2.dbo.SB_PATIENT_EPISODE E1 INNER JOIN
RISBILL.PLATYPUS2.dbo.SB_PATIENT_REGISTER P1 ON
E1.SB_PATIENT_MRN = P1.SB_PATIENT_MRN AND
E1.SB_HL7_SOURCE = P1.SB_HL7_SOURCE LEFT OUTER JOIN
RISBILL.PLATYPUS2.dbo.SB_DEBTOR D1 ON
P1.SB_DEBTOR_ID = D1.SB_DEBTOR_ID LEFT OUTER JOIN
SB_PATIENT_REGISTER P2 ON
E1.SB_PATIENT_MRN collate SQL_Latin1_General_CP1_CI_AS = P2.SB_PATIENT_MRN AND
E1.SB_HL7_SOURCE collate SQL_Latin1_General_CP1_CI_AS = P2.SB_HL7_SOURCE
WHERE
E1.SB_EPISODE_TYPE_CODE = 'I' AND
P2.SB_PATIENT_MRN IS NULL
DECLARE @DEBTOR_ID NUMERIC (18, 0)
DECLARE @SB_PATIENT_MRN varchar (10)
DECLARE @SB_HL7_SOURCE varchar (10)
DECLARE @SB_PATIENT_MEDICARE_NUMBER varchar (10)
DECLARE @SB_CURRENT_HEALTH_FUND_NO varchar (20)
DECLARE @SB_PATIENT_SURNAME varchar (30)
DECLARE @SB_PATIENT_OTHER_NAMES varchar (30)
DECLARE @SB_PATIENT_DOB datetime
DECLARE @SB_PATIENT_TITLE varchar (4)
DECLARE @SB_PATIENT_ALIVE varchar (1)
DECLARE @SB_PATIENT_CREATE_DATE datetime
DECLARE @SB_PATIENT_SEX varchar (1)
DECLARE @SB_HEALTH_FUND_CODE varchar (10)
DECLARE @SB_REDIRECTED_TO varchar (10)
DECLARE @SB_MEDICARE_NO_REF varchar (1)
DECLARE @SB_PATIENT_SURNAME_FUND varchar (30)
DECLARE @SB_PATIENT_UPI varchar (20)
DECLARE @SB_PREPAY_OUTSTANDING numeric(18, 2)
DECLARE @SB_DVA_NUMBER varchar (9)
DECLARE @SB_PATIENT_OTHER_NAMES_FUND varchar (30)
DECLARE @SB_FUND_CARD_REF_NO varchar (2)
DECLARE @SB_UPDATED_BY varchar (20)
DECLARE @SB_UPDATE_DATE datetime
DECLARE @SB_POST_MERGE_MRN varchar (10)
DECLARE @SB_MERGE_DATE datetime
DECLARE @SB_PAT_ADDRESS_1 varchar (50)
DECLARE @SB_PAT_ADDRESS_2 varchar (50)
DECLARE @SB_PAT_SUBURB varchar (30)
DECLARE @SB_PAT_STATE varchar (3)
DECLARE @SB_PAT_POST_CODE varchar (8)
DECLARE @SB_PAT_PHONE_1 varchar (15)
DECLARE @SB_PAT_PHONE_2 varchar (15)
DECLARE @SB_PENSION_NUMBER varchar (15)
DECLARE @SB_SAFETY_NET_NUMBER varchar (15)
DECLARE @SB_SAFETY_NET_EXPIRY datetime
DECLARE @SB_DIALYSIS_PATIENT varchar (1)
DECLARE @SB_PAT_COMMENT varchar (100)
DECLARE @SB_DEBTOR_ID numeric(18, 0)
DECLARE @SB_HIC_ALT_FIRST_NAME varchar (30)
DECLARE @SB_HIC_ALT_SURNAME varchar (30)
DECLARE @SB_HIC_ALT_SEX varchar (1)
DECLARE @SB_DVA_CARD_COLOUR varchar (1)
DECLARE @SB_PATIENT_COUNTRY varchar (30)
DECLARE @SB_PREV_MEDICARE_NO varchar (10)
DECLARE @SB_PREV_MEDICARE_REF varchar (1)
DECLARE @SB_PREV_DVA_NO varchar (9)
DECLARE @SB_PENSIONER varchar (1)
DECLARE @SB_DVA varchar (1)
DECLARE @SB_DEBTOR_COMPANY_NAME varchar (50)
DECLARE @SB_DEBTOR_ADDRESS_1 varchar (50)
DECLARE @SB_DEBTOR_ADDRESS_2 varchar (50)
DECLARE @SB_DEBTOR_SUBURB varchar (30)
DECLARE @SB_DEBTOR_STATE varchar (3)
DECLARE @SB_DEBTOR_POSTCODE varchar (8)
DECLARE @SB_DEBTOR_CONTACT_SURNAME varchar (30)
DECLARE @SB_DEBTOR_CONTACT_GIVEN_NAME varchar (30)
DECLARE @SB_DEBTOR_CONTACT_TITLE varchar (4)
DECLARE @SB_DEBTOR_PHONE_1 varchar (15)
DECLARE @SB_DEBTOR_PHONE_2 varchar (15)
DECLARE @SB_DEBTOR_EMAIL varchar (100)
DECLARE @SB_STANDARD_DEBTOR varchar (1)
DECLARE @SB_GST_MAY_APPLY varchar (1)
DECLARE @SB_DEBTOR_CODE varchar (10)
DECLARE @SB_CURRENT varchar (1)
DECLARE @SB_DEBTOR_COUNTRY varchar (30)
DECLARE @SB_DEBTOR_MOBILE varchar (15)
OPEN PATIENT_CURSOR
BEGIN
-- READ FIRST FROM CURSOR
FETCH NEXT FROM PATIENT_CURSOR
INTO
@SB_PATIENT_MRN
,@SB_HL7_SOURCE
,@SB_PATIENT_MEDICARE_NUMBER
,@SB_CURRENT_HEALTH_FUND_NO
,@SB_PATIENT_SURNAME
,@SB_PATIENT_OTHER_NAMES
,@SB_PATIENT_DOB
,@SB_PATIENT_TITLE
,@SB_PATIENT_ALIVE
,@SB_PATIENT_CREATE_DATE
,@SB_PATIENT_SEX
,@SB_HEALTH_FUND_CODE
,@SB_REDIRECTED_TO
,@SB_MEDICARE_NO_REF
,@SB_PATIENT_SURNAME_FUND
,@SB_PATIENT_UPI
,@SB_DVA_NUMBER
,@SB_PATIENT_OTHER_NAMES_FUND
,@SB_FUND_CARD_REF_NO
,@SB_POST_MERGE_MRN
,@SB_MERGE_DATE
,@SB_PAT_ADDRESS_1
,@SB_PAT_ADDRESS_2
,@SB_PAT_SUBURB
,@SB_PAT_STATE
,@SB_PAT_POST_CODE
,@SB_PAT_PHONE_1
,@SB_PAT_PHONE_2
,@SB_PENSION_NUMBER
,@SB_SAFETY_NET_NUMBER
,@SB_SAFETY_NET_EXPIRY
,@SB_DIALYSIS_PATIENT
,@SB_PAT_COMMENT
,@SB_HIC_ALT_FIRST_NAME
,@SB_HIC_ALT_SURNAME
,@SB_HIC_ALT_SEX
,@SB_DVA_CARD_COLOUR
,@SB_PATIENT_COUNTRY
,@SB_PREV_MEDICARE_NO
,@SB_PREV_MEDICARE_REF
,@SB_PREV_DVA_NO
,@SB_PENSIONER
,@SB_DVA
,@SB_DEBTOR_COMPANY_NAME
,@SB_DEBTOR_ADDRESS_1
,@SB_DEBTOR_ADDRESS_2
,@SB_DEBTOR_SUBURB
,@SB_DEBTOR_STATE
,@SB_DEBTOR_POSTCODE
,@SB_DEBTOR_CONTACT_SURNAME
,@SB_DEBTOR_CONTACT_GIVEN_NAME
,@SB_DEBTOR_CONTACT_TITLE
,@SB_DEBTOR_PHONE_1
,@SB_DEBTOR_PHONE_2
,@SB_DEBTOR_EMAIL
,@SB_STANDARD_DEBTOR
,@SB_GST_MAY_APPLY
,@SB_DEBTOR_CODE
,@SB_CURRENT
,@SB_DEBTOR_COUNTRY
,@SB_DEBTOR_MOBILE
select @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
set @debtor_id =
(
select
TOP 1
SB_DEBTOR_ID
from
SB_DEBTOR
where
SB_DEBTOR_CONTACT_SURNAME = @SB_DEBTOR_CONTACT_SURNAME and
SB_DEBTOR_ADDRESS_1 = @SB_DEBTOR_ADDRESS_1 and
SB_DEBTOR_SUBURB = @SB_DEBTOR_SUBURB and
SB_DEBTOR_POSTCODE = @SB_DEBTOR_POSTCODE
 
if @debtor_id is null
begin
INSERT INTO SB_DEBTOR
(
SB_DEBTOR_COMPANY_NAME,
SB_DEBTOR_ADDRESS_1,
SB_DEBTOR_ADDRESS_2,
SB_DEBTOR_SUBURB,
SB_DEBTOR_STATE,
SB_DEBTOR_POSTCODE,
SB_DEBTOR_CONTACT_SURNAME,
SB_DEBTOR_CONTACT_GIVEN_NAME,
SB_DEBTOR_CONTACT_TITLE,
SB_DEBTOR_PHONE_1,
SB_DEBTOR_PHONE_2,
SB_DEBTOR_EMAIL,
SB_STANDARD_DEBTOR,
SB_CURRENT,
SB_DEBTOR_COUNTRY,
SB_UPDATED_BY,
SB_UPDATE_DATE,
SB_DEBTOR_MOBILE
 
VALUES
(
@SB_DEBTOR_COMPANY_NAME,
@SB_DEBTOR_ADDRESS_1,
@SB_DEBTOR_ADDRESS_2,
@SB_DEBTOR_SUBURB,
@SB_DEBTOR_STATE,
@SB_DEBTOR_POSTCODE,
@SB_DEBTOR_CONTACT_SURNAME,
@SB_DEBTOR_CONTACT_GIVEN_NAME,
@SB_DEBTOR_CONTACT_TITLE,
@SB_DEBTOR_PHONE_1,
@SB_DEBTOR_PHONE_2,
@SB_DEBTOR_EMAIL,
@SB_STANDARD_DEBTOR,
@SB_CURRENT,
@SB_DEBTOR_COUNTRY,
'migration',
GETDATE(),
@SB_DEBTOR_MOBILE
 
SET @DEBTOR_ID = SCOPE_IDENTITY()
end
INSERT INTO SB_PATIENT_REGISTER
(
SB_PATIENT_MRN,
SB_HL7_SOURCE,
SB_PATIENT_MEDICARE_NUMBER,
SB_CURRENT_HEALTH_FUND_NO,
SB_PATIENT_SURNAME,
SB_PATIENT_OTHER_NAMES,
SB_PATIENT_DOB,
SB_PATIENT_TITLE,
SB_PATIENT_ALIVE,
SB_PATIENT_CREATE_DATE,
SB_PATIENT_SEX,
SB_HEALTH_FUND_CODE,
SB_REDIRECTED_TO,
SB_MEDICARE_NO_REF,
SB_PATIENT_SURNAME_FUND,
SB_PATIENT_UPI,
SB_PREPAY_OUTSTANDING,
SB_DVA_NUMBER,
SB_PATIENT_OTHER_NAMES_FUND,
SB_FUND_CARD_REF_NO,
SB_UPDATED_BY,
SB_UPDATE_DATE,
SB_POST_MERGE_MRN,
SB_MERGE_DATE,
SB_PAT_ADDRESS_1,
SB_PAT_ADDRESS_2,
SB_PAT_SUBURB,
SB_PAT_STATE,
SB_PAT_POST_CODE,
SB_PAT_PHONE_1,
SB_PAT_PHONE_2,
SB_PENSION_NUMBER,
SB_SAFETY_NET_NUMBER,
SB_SAFETY_NET_EXPIRY,
SB_DIALYSIS_PATIENT,
SB_PAT_COMMENT,
SB_DEBTOR_ID,
SB_HIC_ALT_FIRST_NAME,
SB_HIC_ALT_SURNAME,
SB_HIC_ALT_SEX,
SB_DVA_CARD_COLOUR,
SB_PATIENT_COUNTRY,
SB_PREV_MEDICARE_NO,
SB_PREV_MEDICARE_REF,
SB_PREV_DVA_NO,
SB_PENSIONER,
SB_DVA
 
VALUES
(
@SB_PATIENT_MRN,
@SB_HL7_SOURCE,
@SB_PATIENT_MEDICARE_NUMBER,
@SB_CURRENT_HEALTH_FUND_NO,
@SB_PATIENT_SURNAME,
@SB_PATIENT_OTHER_NAMES,
@SB_PATIENT_DOB,
@SB_PATIENT_TITLE,
@SB_PATIENT_ALIVE,
@SB_PATIENT_CREATE_DATE,
@SB_PATIENT_SEX,
@SB_HEALTH_FUND_CODE,
@SB_REDIRECTED_TO,
@SB_MEDICARE_NO_REF,
@SB_PATIENT_SURNAME_FUND,
@SB_PATIENT_UPI,
@SB_PREPAY_OUTSTANDING,
@SB_DVA_NUMBER,
@SB_PATIENT_OTHER_NAMES_FUND,
@SB_FUND_CARD_REF_NO,
'migration',
getdate(),
@SB_POST_MERGE_MRN,
@SB_MERGE_DATE,
@SB_PAT_ADDRESS_1,
@SB_PAT_ADDRESS_2,
@SB_PAT_SUBURB,
@SB_PAT_STATE,
@SB_PAT_POST_CODE,
@SB_PAT_PHONE_1,
@SB_PAT_PHONE_2,
@SB_PENSION_NUMBER,
@SB_SAFETY_NET_NUMBER,
@SB_SAFETY_NET_EXPIRY,
@SB_DIALYSIS_PATIENT,
@SB_PAT_COMMENT,
@DEBTOR_ID,
@SB_HIC_ALT_FIRST_NAME,
@SB_HIC_ALT_SURNAME,
@SB_HIC_ALT_SEX,
@SB_DVA_CARD_COLOUR,
@SB_PATIENT_COUNTRY,
@SB_PREV_MEDICARE_NO,
@SB_PREV_MEDICARE_REF,
@SB_PREV_DVA_NO,
@SB_PENSIONER,
@SB_DVA
 
-- READ NEXT FROM CURSOR
FETCH NEXT FROM PATIENT_CURSOR
INTO
@SB_PATIENT_MRN
,@SB_HL7_SOURCE
,@SB_PATIENT_MEDICARE_NUMBER
,@SB_CURRENT_HEALTH_FUND_NO
,@SB_PATIENT_SURNAME
,@SB_PATIENT_OTHER_NAMES
,@SB_PATIENT_DOB
,@SB_PATIENT_TITLE
,@SB_PATIENT_ALIVE
,@SB_PATIENT_CREATE_DATE
,@SB_PATIENT_SEX
,@SB_HEALTH_FUND_CODE
,@SB_REDIRECTED_TO
,@SB_MEDICARE_NO_REF
,@SB_PATIENT_SURNAME_FUND
,@SB_PATIENT_UPI
,@SB_DVA_NUMBER
,@SB_PATIENT_OTHER_NAMES_FUND
,@SB_FUND_CARD_REF_NO
,@SB_POST_MERGE_MRN
,@SB_MERGE_DATE
,@SB_PAT_ADDRESS_1
,@SB_PAT_ADDRESS_2
,@SB_PAT_SUBURB
,@SB_PAT_STATE
,@SB_PAT_POST_CODE
,@SB_PAT_PHONE_1
,@SB_PAT_PHONE_2
,@SB_PENSION_NUMBER
,@SB_SAFETY_NET_NUMBER
,@SB_SAFETY_NET_EXPIRY
,@SB_DIALYSIS_PATIENT
,@SB_PAT_COMMENT
,@SB_HIC_ALT_FIRST_NAME
,@SB_HIC_ALT_SURNAME
,@SB_HIC_ALT_SEX
,@SB_DVA_CARD_COLOUR
,@SB_PATIENT_COUNTRY
,@SB_PREV_MEDICARE_NO
,@SB_PREV_MEDICARE_REF
,@SB_PREV_DVA_NO
,@SB_PENSIONER
,@SB_DVA
,@SB_DEBTOR_COMPANY_NAME
,@SB_DEBTOR_ADDRESS_1
,@SB_DEBTOR_ADDRESS_2
,@SB_DEBTOR_SUBURB
,@SB_DEBTOR_STATE
,@SB_DEBTOR_POSTCODE
,@SB_DEBTOR_CONTACT_SURNAME
,@SB_DEBTOR_CONTACT_GIVEN_NAME
,@SB_DEBTOR_CONTACT_TITLE
,@SB_DEBTOR_PHONE_1
,@SB_DEBTOR_PHONE_2
,@SB_DEBTOR_EMAIL
,@SB_STANDARD_DEBTOR
,@SB_GST_MAY_APPLY
,@SB_DEBTOR_CODE
,@SB_CURRENT
,@SB_DEBTOR_COUNTRY
,@SB_DEBTOR_MOBILE
END
END
CLOSE PATIENT_CURSOR
DEALLOCATE PATIENT_CURSOR
-
Why not go a different route.
DTS the new records in a temp table on the local server (so you go on the other server only once).
Then insert into debtor where not exists ()
Then insert the patients... That should all be done in a few seconds (depending on the size of the 2 tables you're inserting into).
-
figured it out in the end.
DECLARE PATIENT_CURSOR CURSOR STATIC READ_ONLY FOR
Usually id use DTS, but I was not sure how to proceed to insert into SB_DEBTOR first, and use the Identity to insert the patient.
-
Throw away the cursor.
INSERT INTO SB_DEBTOR
SELECT ....
WHERE NOT EXISTS (select
SB_DEBTOR_ID
from
SB_DEBTOR
where
SB_DEBTOR_CONTACT_SURNAME = D1.SB_DEBTOR_CONTACT_SURNAME and
SB_DEBTOR_ADDRESS_1 = D1.SB_DEBTOR_ADDRESS_1 and
SB_DEBTOR_SUBURB = D1.SB_DEBTOR_SUBURB and
SB_DEBTOR_POSTCODE = D1.SB_DEBTOR_POSTCODE
)
INSERT INTO SB_PATIENT_REGISTER
SELECT
FROM < joins you use for select>
INNER JOIN SB_DEBTOR ON
SB_DEBTOR_CONTACT_SURNAME = D1.SB_DEBTOR_CONTACT_SURNAME and
SB_DEBTOR_ADDRESS_1 = D1.SB_DEBTOR_ADDRESS_1 and
SB_DEBTOR_SUBURB = D1.SB_DEBTOR_SUBURB and
SB_DEBTOR_POSTCODE = D1.SB_DEBTOR_POSTCODE
50 times better performance guarantee.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply