1

I am trying to use Export data option to copy data from source to destination table wise, but it throws an error if there is any foreign key relationship, this error is because, if I selected a parent table and tried to push data to the destination without selecting child table, it will throw an error because the new id information which is in the parent table is not present in the child table of the destination database.

How can I copy all the tables in the specific schema with data to the destination database schema by handling foreign key relationship? (The schema names and schema of tables are the same in both the databases)

I am using SSMS, is there any script that can do this job as I have more than 1000 tables in the database, it will be hard to manually select all tables and copy them.

So I am interested in the second option!

enter image description here

Foreign key Error message:

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table1_Table2". The conflict occurred in database "DevDB", table "schema_1.Table2", column 'column_name'.". (SQL Server Import and Export Wizard)

Improve this question
2

1 Answer 1

Reset to default
2

The SQL Server Import and Export Wizard in SSMS is old at this point, and can be a pain. There's a multitude of ways to copy data between databases but I'll mention the simplest of the two below:

  1. Restore a backup - If you're ok with replacing everything in the DEV database with the latest from PROD, then you can simply just restore the latest backup of the PROD database to your DEV database. If there are newer changes to the copy of the DEV database that you need to preserve, then those should be created as batches of scripts, so that they can be re-applied after the backup is restored. With that kind of workflow, it's important that all changes made in DEV are always continually off the latest version of PROD as the base.

  2. Use a data / schema comparison tool - This will allow you to synchronize only the objects you care about, and their dependencies (if you choose). Then you'd be able to synchronize both parent and child tables of a foreign key relationship concurrently. There are multiple tools out there that do this, but my personal preference is SQL Examiner Suite because it is simple but robust enough, works well, supports multipe database systems, and is relatively cheap.

Improve this answer
3
  • Is there any other way to update only specific schema without using other paid tools? The database is created using azure SQL server, so any thing from azure services or from sql server management studio works to us. Is there any such option available?
    – Python coder
    Mar 20 at 11:45
  • @Pythoncoder Azure SQL Database is different than SQL Server (please tag it appropriately next time). Unfortunately the cloud database services are rather limited in their features, so I don't really know of any other ways. The only other idea you could look into is CDC - Change Data Capture and setup some kind of job to copy the changes. But that's only available if you're paying for an S3 tier Azure instance. It's probably cheaper to buy the tool I linked.
    – J.D.
    Mar 20 at 12:04
  • 1
    Thanks for sharing the info, I have updated the tag as well.
    – Python coder
    Mar 20 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.