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.
In conclusion options do exist to support tables with the same name from different schemas/name spaces however no option is perfect. The best choice for an environment must be chosen based on the needs of the environment.
Here, a database can have multiple schemas (or “schemata,” if you're feeling fancy). Each one contains all the objects created by a specific database user. Those objects may include tables, views, synonyms, and more. Some objects cannot be included in a schema, such as users, contexts, roles, and directory objects.
New Table...
Try running CREATE TABLE [schemaname].[tableName]; GO;
This assumes the schemaname exists in your database. Please use CREATE SCHEMA [schemaname]
if you need to create a schema as well.
EDIT: updated to note SQL Server 11.03 requiring this be the only statement in the batch.
create a database schema in SQL Server 2008
1. Navigate to Security > Schemas
2. Right click on Schemas and select New Schema
3. Complete the details in the General tab for the new schema. Like, the schema name is "MySchema" and the schema owner is "Admin".
4. Add users to the schema as required and set their permissions:
5. Add any extended properties (via the Extended Properties tab)
6. Click OK.
Add a Table to the New Schema "MySchema"
1. In Object Explorer, right click on the table name and select "Design":
2. Changing database schema for a table in SQL Server Management Studio
3. From Design view, press F4 to display the Properties window.
4. From the Properties window, change the schema to the desired schema:
5. Close Design View by right clicking the tab and selecting "Close":
6. Closing Design View
7. Click "OK" when prompted to save
8. Your table has now been transferred to the "MySchema" schema.
Refresh the Object Browser view To confirm the changes
Done
Hit F4 and you'll get what you are looking for.
Shaun F's answer will not work if Schema doesn't exist in the DB. If anyone is looking for way to create schema then just execute following script to create schema.
create schema [schema_name]
CREATE TABLE [schema_name].[table_name](
...
) ON [PRIMARY]
While adding new table, go to table design mode and press F4
to open property Window and select the schema from dropdown. Default is dbo
.
You can also change the schema of the current Table using Property window.
Refer:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With