Transfer table to Schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transfer table to Schema

Author

Topic

mallorz4
Starting Member

27 Posts

Posted - 2014-03-05 : 19:44:47

I created a schema, Admin. I have to transfer a table from the dbo schema to the admin schema. I keep getting an error that I do not have permission or the table does not exist.

Simply looking for confirmation here - is my syntax correct?

ALTER SCHEMA Admin TRANSFER MyShop.Addresses;

(MyShop is the Database, Addresses is the table)

NOTE: When I created the schema, I did not create an inner table. The syntax for that was simply

 CREATE SCHEMA Admin;

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-05 : 20:08:45

USE MyShop
GO
ALTER SCHEMA Admin TRANSFER dbo.Addresses;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mallorz4
Starting Member

27 Posts

Posted - 2014-03-05 : 20:20:44

quote:
Originally posted by tkizer

USE MyShop
GO
ALTER SCHEMA Admin TRANSFER dbo.Addresses;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Thanks for the reply. I get the same result.

I expanded the Databases tree and dug into MyShop. While all of the other tables in the database read dbo.X, the Addresses table reads Admin.Addresses. Is it possible I somehow altered that while messing with this statement? If so, how can I change it back?

I'll be back at this tomorrow night, probably signing off for the evening.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-05 : 22:18:21

If it says Admin.Addresses, isn't it already where you want it to be?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mallorz4
Starting Member

27 Posts

Posted - 2014-03-06 : 06:52:27

quote:
Originally posted by tkizer

If it says Admin.Addresses, isn't it already where you want it to be?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Should it say that under the MyShop database then? I never got a 'successful' execution which is concerning. Maybe I should confirm with my instructor ..?

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-03-06 : 11:16:57

To verify your success, write a query including Admin.Addresses. If it succeeds, then bingo.

=======================
Not an Expert, Just a learner.
!_(M)_!

mallorz4
Starting Member

27 Posts

Posted - 2014-03-06 : 17:24:57

quote:
Originally posted by maunishq

To verify your success, write a query including Admin.Addresses. If it succeeds, then bingo.

=======================
Not an Expert, Just a learner.
!_(M)_!

SELECT * FROM Admin.Addresses;

.. Fails (Invalid object name)

USE MyShop
SELECT * FROM Admin.Addresses;

Succeeds with contents of Addresses table.

This would be the same as saying..

USE MyShop
SELECT * FROM Addresses;

.. so what's the point of transferring the table to the schema? I suppose this is all correct then? I guess I don't completely understand this exercise.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-06 : 18:29:25

Your first one fails because you aren't in the correct database. If you want to query it from the wrong database, you need to supply the database name: select * from MyShop.Admin.Addresses

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mallorz4
Starting Member

27 Posts

Posted - 2014-03-06 : 18:36:53

Okay. Syntax-wise it looks right to me and it seems like it is so I'll roll with it.

Thank you everyone for the replies!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources