mezamorphic mezamorphic - 1 year ago 126
SQL Question

Copy table(s) with data from one schema to another?

I have a number of tables with data in

Schema1
and I would like to copy these tables, with the data, to a new schema I have created,
Schema2
.

Is there an elegant way of doing this? I am using SQL Server Management studio.

Answer Source

In SQL Management studio right click the database that has the source table, select Tasks -> Export data.

You will be able to set source and destination server and schema, select the tables you wish to copy and you can have the destination schema create the tables that will be exported.

Also, if copying to the same server and database but different schema be sure to:

  1. Use the Sql Server Native Client (see
    https://i.stack.imgur.com/Qqhbd.png) for Source and Destination
    parameters
  2. Select the same database name for the Source and Destination parameters
  3. Choose copy data from one or more tables or views (optional) In the Select Source Tables and Views GUI
  4. Change the destination table's schema to something different than the source schema (i.e. type something like "newschema.tablename")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download