Copy table(s) with data from one schema to another?
sql, sql-server, sql-server-2008
Solution
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:
- Use the Sql Server Native Client (see https://i.stack.imgur.com/Qqhbd.png) for Source and Destination parameters
- Select the same database name for the Source and Destination parameters
- Choose copy data from one or more tables or views (optional) In the Select Source Tables and Views GUI
- Change the destination table's schema to something different than the source schema (i.e. type something like "newschema.tablename")
Problem
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.