How do I quickly move all records between two identical tables in different databases?


This is a simple process using an INSERT INTO query except for the need to create the column list which is just tedious for tables with many columns. Here is a shortcut:

  1. Open Microsoft SQL Server Managment Studio
  2. Go to the target database and table list in Object Explorer
  3. Right-click the table and click Script Table as | Insert To | New Query Window
  4. Delete everything from Values down
  5. Type "select from {other database}..{tablename} (nolock)" at the bottom and hit enter
  6. Copy the column list from the top of the query (everything between the parentheses but not the parens themselves) and paste it between the "select from" you just typed.
  7. Final query should follow the format "select {column list} from {other database}..{tablename} (nolock)." For example:
    • INSERT INTO [dbo].[land]
      from other_database..tablename (nolock)
  8. Run the query