herkusg - Wednesday, February 21, 2018 4:26 PM

Hello all,

I was recently assigned to convert an Access database to Sql Server.  I have to try and keep the structure the closest properly because some other programs use this database.

This concrete table got a composite key for Company, Warehouse, ManufacturingID but the ID part starts over with each combination of Company and warehosue.

Example:
Company | Warehouse | ManufacturingID | ...
AU          |  w01           |  1
AU          |  w01           |  2
AU          |  w04           |  1
AU          |  w04           |  2
NZ          |  w01           |  1
NZ          |  w02           |  2

The access program does this manually which I don't want to replicate because it would just cause troubles..

I am looking for options on how to do this, preferably with not having to change much of the table so the other programs would be easy to adapt.

Reading around a bit I think one possible solution could be to use a INSTEAD OF INSERT trigger, is this right?

Thanks for any help.