Define empty tables in the database and paste data from another table into another database

Question

Actual scenario . I have a duplicate database and want to know in which tables the data was not migrated from the original database. Then I want to populate the Duplicate tables from the original.

I reached my partial solution (finding empty tables) by typing the following script:

SELECT DBDupl.[dbo].sysobjects.Name, DBDupl.[dbo].sysindexes.Rows FROM DBDupl.[dbo].sysobjects INNER JOIN DBDupl.[dbo].sysindexes ON DBDupl.[dbo].sysobjects.id = DCT_SOURCE_QA.[dbo].sysindexes.id WHERE type = 'U' AND DBDupl.[dbo].sysindexes.IndId < 2 and rows= '0' EXCEPT SELECT DBOrig.[dbo].sysobjects.Name, DBOrig.[dbo].sysindexes.Rows FROM DBOrig.[dbo].sysobjects INNER JOIN DBOrig.[dbo].sysindexes ON DBOrig.[dbo].sysobjects.id = DBOrig.[dbo].sysindexes.id WHERE type = 'U' AND DBOrig.[dbo].sysindexes.IndId < 2 and rows= '0' 

Now I want to fill in the data in empty tables. Is there a single request to execute like ie (1) things. Determining which tables should be completed, and (2). Fill in the data from DBOrig to DBDupl. I reached (1) with the above script and don't want to insert data manually.

Answer 1

Do you want an exact copy of the database? why not back up the main and restore with a different name?

If this is not possible, you can use SSIS to move the data or something like:

 insert into destDB.dbo.mytable select * sourcedb.dbo.mytable 

you can use your script to scroll through empty tables and run some dynamic SQL to execute the command above