I tried to use Export option from SSMS which gives an error saying failed due to foreign key relationship.
How to copy data between tables(more than 400+) of two different azure sql server with has the same schema? It's fine to truncate data before copying the data as it is a full load.
Error:
TITLE: SQL Server Import and Export Wizard
Error 0xc002f210: Preparation SQL Task 1: Executing the query "TRUNCATE TABLE [dbo].[table_name] " failed with the following error: "Cannot truncate table 'dbo.table_name' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
My suggestion is to use SQL Data Sync to replicate data between the two tables. You could replicate the data in one-direction or bi-directional. You can create a sync group involving the tables and other related tables needed. You can use SQL Data Sync to copy the data one time and get an initial load of data or to keep the tables in sync. Frequency of the sync could be seonds, minutes, hours, days or manual.
You will have to regularly checking the sync group’s history log and ensure that all conflicts are resolved, and changes are successfully propagated throughout the sync group databases. For example, a sync group may fail to update a row with a value that violates a foreign key constraint. In that scenario, you can update the foreign key values to include the values contained in the failed rows or you update the data values in the failed row to be compatible with the schema or foreign keys in the target database. After that the sync group will continue to replicate the data between the two tables.
Here you will find how to get started with SQL Data Sync.