Hi,
I would like to know if there is a better way to enter committee and section member information into iMIS. 95% of our Committee and Section members are the same so we would like to try to avoid re-entering them into iMIS manually if possible. I had tried doing INSERT INTO SELECT statement from a temp table to Activity table but we cannot add new members into iMIS after the bulk inserts. The error I got was about "cannot insert duplicate key in Activity table. The primary key for Activity table is "SEQN" and I have also supplied the unique numbers in my temp table. Not sure what went wrong. Is there any other tables I would need to watch out for besides Activity table? Has anyone had a better way to import the activity records? The "Import Activities" in iMIS can only import records with the same data values, which is no use for us.
Thanks.
Wen-Lu
IT & Database Manager
PIAA
Counters
Activity (like many other tables) uses the Counter table to assign new unique values. When iMIS inserts a new row, it uses sp_asi_GetCounter (or similar code) to reserve a new number before it adds it to the table.
It's best if you can use the same technique. If you must insert rows a different way, it's best if you bump the correct counter forward first, then insert your new data using the reserved values.
If this is something you will be doing often, you should look at turning this into a stored procedure or other programming:
1. Import raw data into a holding table you created with an identity field.
2. Bump the counter forward by the number of rows you have. This will reserve a block. Make careful note of the value of Counter.LAST_VALUE before and after your update.
3. Insert your data into Activity, using [old counter value] + [identity field] for each row. This should assign values from [old counter value] + 1 through [new counter value] to your rows.
This description is very simplistic, and assumes that nobody else is adding data while you perform step 2.
Some people will also recommend doing a Rebuild Counter after any import. You can do that, but that leaves a larger window of opportunity for someone else to sneak in and assign one of the values you want.
--
Bruce Wilson
Director, Technical Services
RSM McGladrey, Inc.