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!
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)