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

Question

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 1

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

Answer 2

Assuming the schema has structural changes, so you can't just do a backup and restore to a new database you can right-mouse click on the database and select tasks | generate scripts. On the second page there is an option (off by default) to script the data.

That creates a series of SQL insert statements along with the CREATE TABLE statements. You may also want to select Script Triggers and check a few of the other options that you may require if using those features.

You can also do an INSERT across schemas or use SSIS as described here:

How can I copy data records between two instances of an SQLServer database

One thing to be aware of is that I believe the export data facility doesn't propagate the indexes, constraints, triggers etc which is why I like to use the first method for moderate amounts of data or at least to create the new tables in the first place followed by one of the other methods.

Answer 3

Here is what I did:

1. right click on source table and select "select top 1000 rows".
2. remove "top 1000" from select.
3. add "INTO newschema.newtable" above the from clause
4. run that query.
5. create the indexes on the new table.

This will not work for everyone because it requires the new schema to be available from the same connection in SQL Studio.