Is there a rule that you can use to identify which rows have been inserted or updated? Perhaps a timestamp column, or other indication that would let you write a queryt that would return only the qualifying rows?
Or, are you thinking about doing an export as part of an insert or update?
I am currently using a last edited datetime stamp. This works but is not perfect.
James K, yes if we could do an export as part of an insert or update that would be good.
To give you a bit more background it is to create CSV files which our Warehouse Management System picks up. Therefore, ideally I would like to create a file on the Stock Code which as just been updated/inserted. I currently have a view which creates the CSV file in the right layout and use BCP to export it. If it was possible to pick up the Stock Code on the line updated or inserted and put that into the where clause of the view inside the BCP string that would work. Sorry I might be going about this the wrong way and there is probably a better way of achieving what I am trying to.
If this table is modified by users then I would not make the export part of the insert/update. That usually means via a trigger which would tie this export to the user transaction. That could make the insert take way too long and if anything failed on the export the user transaction would be rolled back. I doubt you want that.
Perhaps a new table or a new column to track what has been sent to the WMS. The export process could be a scheduled sql job. That export process would send all the rows that haven't been sent yet and of course update the "what has been sent" info. You'll have to build in something to detect when the export or the WMS import process fails for any reason so that your "what has been sent" info is accurate for what has actually arrived.