Hi. I've been having a problem with replication for some time now that I finally need to see if there's a better way to deal with. My entire setup is SQL 2008; Standard on the server (publisher) and Express on the laptops (subscribers); I'm using Merge pull subscriptions and my situation is a lot like the classic travelling salesman example from the SQL docs (hence why I'm using Merge Pull).
Let's say I want to accomplish the following. I have an existing database, with data in it, with Table A and Table B which have no relationship. However, I'd like to do add a column, named Foo, to Table B which references the PK of Table A. Furthermore, I'd like add a row to Table A, then make that the PK of that row the default value of the new column in Table B (all existing rows in B would have this value). Finally, the new column in Table B must be set NOT NULL.
Normally, to do the above, I'd write a script like so to accomplish it:
a) Add row in Table A
b) Get ID of new row using @@Identity