MSF210 and Data Loading

[Mainco]

We're in the process of splitting a distrcit into two seperate districts, we've identified data in MSF210 that needs to be added to the new district. In a number of cases we're using a SQL bulk insert statement to get the data into MIMS. In this case we're getting the following error message. "Cannont insert duplicate key row into object MSF210 with unique index MSF210001IXO. Any ideas on how to get round this?

 

[ndoreka]

I might have thought you have to actually add a new district from the table msf000_ad , this will give you all the active districts.

 

[Mainco]

Just to Clarify. The distrcit has already been set up, what we're doing is moving some of the data from the old district to the new district.

 

[Calator]

because MSF21001IX0 is the primary key of MSF210:
INDEX_NAME COLUMN_NAME
------------------------------ ------------
MSF21001IX0 DSTRCT_CODE
MSF21001IX0 STOCK_CODE
MSF21001IX0 SUPPLIER_NO
MSF21001IX0 PRICE_CODE

I think you need to check on the records you attempt to load, for the possible situations:
- at least one record still has the original district code
- all records have been converted to the new distrcit code, but somehow you have a duplicate record key (two records wuith same district, stock code, supplier and price code)

 

[PhantomPhil]

Perhaps post your SQL code here. Otherwise any potential helpful responses will just be guesses.

For example, you may currently have multiple records with the sample stock_code, supplier_no and price_code combination (different districts), and if you are merely copying them all but inserting your new district code, then you will get a duplicate key error.

 

[Mainco]

Essentially this is the SQL that is being used (i've removed much of the select portion fro brevities sake.

SELECT A.DSTRCT_CODE, A.STOCK_CODE , A.SUPPLIER_NO , A.PRICE_CODE , A.FPA_REC_TYPE , A.FPA_ID , A.FPA_ITEM_NO,
A.PREV_EFF_DATE_4 , A.PREV_EXP_DATE_4, B.DSTRCT_CODE, B.STOCK_CODE
FROM MSF210 A, MSF170 B
WHERE B.DSTRCT_CODE = 'MAIN' AND
A.STOCK_CODE = B.STOCK_CODE

I take the data extracted and filter out certain records that are not going to be translated over to the new distrcit and bulk insert it.

I believe falling into the trap that Phantom Phil describes.

Alan