best approach to migrating a table from system to another

Question

I have a test system where we update entries on a table for cross refence purpose and was wondering the best approach for this.

Answer 1

Hi @Jonathan Brotto,

Thanks for your information.

1. Using Generate Scripts. Here is an article containing the detailed steps, hope it can help you well.
2. Using the Data Migration Assistant tool. Please check out this article, you can follow it step by step, hope it can help you well.
3. In addition, for the linked servers, here is a link, https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16, hope it can help you understand.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

If you have a central copy of a table that's holding reference data that's needed in other systems, there are now basically three ways of dealing with this:

• First, if you want a local copy of the data in each other system, transactional replication works fine for this sort of thing. (Note: I'm not saying merge replication) You can have a bunch of systems that subscribe to a central system. The publications can be push or pull (i.e., the central system can push out the data as it changes, or the other systems can periodically pull the data from the central system. Some people see replication as complex but generally I find that's simply because they don't understand it. For this sort of purpose, it's often a case of setting it up and almost forgetting it from that point on. We have online on-demand traning on it if you need extra knowledge: https://sqldownunder.com/courses/sql-server-replication-for-developers-and-dbas You can replicate one or more tables, and even down to the column level if needed.
• Second, if you are working with SQL Server on-premises or in VMs, you can set up linked servers. This allows you to refer to tables on a remote server. You could include those tables in your main queries, or you could periodically pull over a local copy of the data. One of the biggest mistakes I see people make with linked servers is not setting collation compatibility where appropriate. That can make a huge difference in performance of queries that include data from remote tables. Make sure you get it right.
• Third, a more modern equivalent than linked servers is the use of external tables. These also work in other platforms like Azure SQL Database. You set up an external data source that's basically details of how to connect to the remote server. You then set up an external table that's like a local view that points at a table or view on a remote server. Again, you can refer to these directly in queries, or periodically pull over local copies of the data if you would prefer that.