I have an interesting situation. We are currently undergoing a conversion from one automation system to another. The new system uses SQL 2000, the old system is a DOS btreive system.
We are able to export our btreive data to an Access database then to SQL. However, we have to make several conversions of the data before we can insert it into the new system.
We are an alarm central station monitoring several thousand alarm systems. Each system has a call list known as contacts, that we call in the event of an alarm. Our new system has a table that contains all of the contacts from all of our accounts. The site number is used so the system knows which contact goes with which site.
Then we have to know what order to call these people. The new system assigns a value in a field called cs_seqno to make this determination. The system steps the sequence numbers by 10. The first person to be called has 10 as the sequence number. The next has 20, the next 30 and so on. This spacing is necessary so we can insert a person at a later date, say 15 to establish a new second person on the call list.
In any case, I have a contact list from our old system that is arranged as it was built. The program that exports the data to an Access database assigns a number to each record. This number can be used to establish the call order. The data is not however arranged by site.
I need to create a list by site then assign a sequence number beginning with 10 and stepping in 10’s to each contact associated with this site. I then need to insert this data in the contact table of our new system.
I won’t have a problem writing a transact SQL routine to accomplish the sorting by site and if the data was being inserted in an individual site table the sequence number would not be a problem. But, I am stumped as to how to create this little subset (contact list associated with a site), assign the sequence number then insert it in the main table and then repeat the process until all contacts have been processed.
If you can provide me any assistance it will be greatly appreciated.
Ron Wies
We are able to export our btreive data to an Access database then to SQL. However, we have to make several conversions of the data before we can insert it into the new system.
We are an alarm central station monitoring several thousand alarm systems. Each system has a call list known as contacts, that we call in the event of an alarm. Our new system has a table that contains all of the contacts from all of our accounts. The site number is used so the system knows which contact goes with which site.
Then we have to know what order to call these people. The new system assigns a value in a field called cs_seqno to make this determination. The system steps the sequence numbers by 10. The first person to be called has 10 as the sequence number. The next has 20, the next 30 and so on. This spacing is necessary so we can insert a person at a later date, say 15 to establish a new second person on the call list.
In any case, I have a contact list from our old system that is arranged as it was built. The program that exports the data to an Access database assigns a number to each record. This number can be used to establish the call order. The data is not however arranged by site.
I need to create a list by site then assign a sequence number beginning with 10 and stepping in 10’s to each contact associated with this site. I then need to insert this data in the contact table of our new system.
I won’t have a problem writing a transact SQL routine to accomplish the sorting by site and if the data was being inserted in an individual site table the sequence number would not be a problem. But, I am stumped as to how to create this little subset (contact list associated with a site), assign the sequence number then insert it in the main table and then repeat the process until all contacts have been processed.
If you can provide me any assistance it will be greatly appreciated.
Ron Wies
