Adding a field to a table (but in another database) I understand that this is a strange question but I'll try to explain it the best I can.

We have a database (I'll call it MachineDB) on our MSSQL Server 2016 that gets many datasets each day from a machine (we're talking ~50k datasets/day and this database has been running for 2 years now). A dataset consists of some fields specific to that machine. Whenever there is a an update from the machine manufacturer this will be a DacPack. I understand that this is basically a schema update which will delete some tables/fields and add others. I understand there are ways to compare those schemas manually and update them manually. 

So, what I would like to do is to add one field of type int to one of the tables (the table has a primary key defined). We would like to use this field to track inventory usage. It would be easy to simply add this field and fill it with the required data.

BUT, whenever an update is rolled out, this field would get deleted because the update process does some kind of schema compare and deletes fields that are not used anymore.

So the actual question is: Is there a method I can add that field into another database which references the primary database or what would be the correct approach to this?
You might be able to get away with just adding a new schema to the existing database, but either way the solution is approximately the same.  

In order to avoid interfering with the dacpac deployment, you may want to avoid actually implementing a true foreign key constraint.  You can either make a stored procedure do the inserts/updates and have it manually do the constraint checking before inserting a new row.  

But basically, create a table with a non-incrementing primary key (that should have a 1:1 with the source table.  Then add your custom fields to that table.  You can use a view to merge them together to make it easier to digest the contents if you want.

CREATE TABLE dbo.Extension
    (
    DatasetID INT NOT NULL PRIMARY KEY --make sure to match type to source
    , InventoryCount INT NULL 
    )


As I said, if you can add it as a custom schema (assuming that survives the dacpac update process the vendor is using) then it can live inside the same database (my preference).  But you can add it into a new database if you want as well.