June 26, 2020 at 4:25 pm
Hi, I'm trying to find out whether is possible to achieve the following with a procedure. Let's say I have two databases A and B. In A I have procedures that make use of tables in B. I'm trying to move the tables used from B to A, but only the fields used by the procedures. So basically within this procedure I would pass the name of the procedure in A then it would check what tables(and fields used) are used from B. Then it would create those tables in B with those fields and if a later procedure in A uses different fields from the same table from B add that field. Finally, nice these procedures in A are calling the tables in B, alter the procedure so to adjust the calling to B to A. Is it possible? Thanks.
June 26, 2020 at 4:50 pm
I expect that is possible, but it would be messy to do and error prone and I expect difficult to maintain.
When doing changes like this, my preference is to make small, easily testable changes and script the whole thing out. This way I can test things and ensure that it works the way I expect.
Problems with automating what you are asking for include things like what if the column isn't aliased in the join? What if multiple stored procedures use different column sets from the table on B? Both of these situations result in making the automation more complicated to work with.
My expectation is that writing and testing and debugging a script to automate this will take longer than just doing it manually.
My other thought with this is you are going to need to do something to keep the tables in A and B in sync, correct? Are you wanting a near real-time sync or a nightly sync? If you are doing a nightly sync, I'd be looking at SSIS for something like this. If you are needing near real-time, service broker may be a better option.
My last thought - is there a NEED to keep both A and B separate? Could you instead merge them and keep things separated via schemas? This would remove the cross database calls.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 26, 2020 at 5:13 pm
Before even going down this road - what is the purpose of copying data from database B into database A? What issue are you trying to resolve by doing this?
Unless database B is actually on another server and you are pulling the data across a linked server - there really isn't any reason to copy the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply