I have a database in SQL Server 2000 that needs to import EDI files from multiple sources for a billing application. In my original design I've used identity columns to acheive this but want to eliminate the headaches associated with inserting into a table with an identity column but would be open to other design considerations. I currently only work with one EDI feed and the file is flat allowing me to insert the records into a table called AIn with AInID as an identity column and using DTS to map the incoming file. I then insert AInID into a table named Batch with an identity column called BatchID creating a database wide unique ID. Then I update AIn.BatchID (int column) by joining on AIn.AInID = Batch.BatchID getting the database wide unique id back to the originally inserted table. By doing this I can do the same thing with other formats of EDI files into tables such as BIn, CIn, etc... and maintain a system wide unique ID. After the BatchID is established I can join quickly to the input table and child tables to Batch such as BatchAddChg. As this data is processed multiple times while customers and charges are identified (both programmatically and manually through a VB interface) I have created a set of tables AInHistory, BatchHistory, BatchAddChgHistory for historical purposes and for data to be posted to the web (with no identity columns, just int columns as PK). The only identity columns in question are on the AIn and Batch tables. Currently updating goes against Batch, usually against one or all records. Easy to copy the data from Batch to BatchHistory but not the other way as the identity column forces the fields to be listed and the DB is still in development. I sometimes want to retrieve the data back to batch so as not to need to rewrite existing code as I'm in a time cruch. I'll be modifying all necessary items to allow me to filter per batch to allow processing on a second batch while waiting on info for the first batch. I've toyed with an insert trigger but need to see what caused my DTS insert to fail when removing the AIn.AInID identity feature and adding the below trigger. BatchID is defaulted to zero and I'm expecting the insert to happen for each record keeping BatchID unique and still a PK. Any suggestions would be much appreciated. Currently we process around 1500 records per week and expect it to grow while maintaining years of history. Performance should be a consideration.
CREATE TRIGGER [AIn_Ins] ON [dbo].[AIn]
After INSERT
AS

Begin Transaction
If (Select Max(BatchID) From Inserted) = '0'
Begin
Update AIn Set BatchID = (Select Max(UniqueNID) From UniqueN) Where BatchID = 0
Update UniqueN Set UniqueNID = (Select(Max(UniqueNID) + 1) From UniqueN)
End
Commit Transaction

Thanks