0

I have two databases in SQL Server 2012 with the same tables. So I have databases A and B with tables a, b, c, d in both.

I would like to delete all the data from B.a and copy the data from A.a into B.a. I would like to do this with all the tables, clear them out in B but keep the tables then refill them with the table data from A.

What is the best way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
David
  • 149
  • 1
  • 4
  • 13
  • 1
    Truncate all table in B. Then insert table B using Insert Table Select From syntax. – Sean Lange Jul 21 '14 at 20:43
  • 3
    Backup DB A and Restore as DB B – T McKeown Jul 21 '14 at 20:44
  • I'd go with T McKeown's response, if that is a viable option. – Dave.Gugg Jul 21 '14 at 20:45
  • Both of the databases have different names. I thought it would not let you restore to a database with a different name. – David Jul 21 '14 at 20:56
  • I tried to do the backup/restore but it did not work. They are two distinct database's I just want to clear one out and move the data to it. – David Jul 21 '14 at 21:04
  • Are you able to query across databases? Maybe they are on the same instance or linked servers? Like from DB B can you query DB A? – SQLChao Jul 21 '14 at 21:07
  • Important: Do you have any IDENTITY columns or any Foreign Keys? If not, then its not too hard. If so, then it can be a ton of work, depending on how many... – RBarryYoung Jul 21 '14 at 21:30
  • Take a look at: http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server . You can do the 4 tables individuals w the copy wizard, or the import/export wizard, I forget which one. Backup restore overrides your target database producing unintended consequences sometimes, so unless your looking to do a restore, stick w the SSMS wizards, or write your own queries, I'd use the wizard. – RandomUs1r Jul 21 '14 at 21:30
  • Do you want the databases `A` and `B` to be exactly the same after you're done (except for the name)? Then I would suggest the backup and restore strategy. If you only need to copy data for a number of tables then I would look into the Import and Export wizard. It can be configured to delete the data first and then load in the new data. – JodyT Jul 21 '14 at 21:32
  • FYI, BACKUP/RESTORE does *not* have a problem with differing database names. – RBarryYoung Jul 21 '14 at 21:34

1 Answers1

0

Here you go:

  1. Back the destination DB. this is as a roll back plan
  2. Export all records from the source database "A" using the following steps
    • Right Click on DB
    • Select Tasks
    • Select Generate Scripts, Wizard dialog will popup
    • From the popup select the tables you would like to export
    • In the "Set Scripting Options" -> Click Advanced -> make sure to set "Types of Data to script" to "Data Only"
    • this will generate script with insert statements
  3. Open the destination database and do the following steps
    • Truncate the destination tables
    • Run the scripts generated from step 2

Hope this helps

Hossam Barakat
  • 1,399
  • 9
  • 19