Remapping Identity Columns

Currently with my Employer we are undertaking a new way to export and import data from 1 system to another via TCP.

The databases that we run as SQLExpress 2008r2 and they exist on the local computer.

Everything within the database is fully referential to the current database, but you cannot use an ID from 1 Database to find a record on another system you have to fall back to the Common Identifier field.

So with this in mind we needed a way to be able to Export Data from 1 system and then take it to another and import it. (Bearing in mind the remote system at the point of import will be Vanilla). So with this in mind and that we may have hundreds of thousands of rows I started writing a tool that would Export the data from the DB and then be able to use SQLBulkCopy to reimport it at the other end.

This identified that if we do this a lot then we will be constantly increasing the Identity column in new databases. This is unnecessary because it's a new database. So I came up with a solution that when exporting remaps the Identity (and foreign key columns pointing at that identity) to initial values.

The inital approach I used was to look carefully at Protobuf-net-data and see if I could sculpt the remapping of the values into the code. After talking with Richard, he suggested that I look at a Decorated Data Reader instead.

So with that I have come up with this.

SqlRemappingDataReader