Transfer table from one schema to another

Question

i have a schema SCHEMA1 with tables TABLE1,TABLE2,TABLE3.

And another schema SCHEMA2 with tables TABLE1,TABLE2.

Now i need to transfer the TABLE2 from SCHEMA1 to SCHEMA2.

It shows error because the table names are same.

How can i solve this?

Answer 1

As Viorel says, you need to decide what you want. If you only want to keep one table, drop the other.

If you want to keep both, you will need to rename one of them. You cannot rename a table with ALTER SCHEMA TRANSFER, but you would need rename the table first.

CREATE TABLE guest.Nilsson (a int NOT NULL)
go
EXEC sp_rename 'guest.Nilsson', 'Karlsson'
ALTER SCHEMA dbo TRANSFER guest.Karlsson
go
SELECT a FROM dbo.Karlsson
DROP TABLE dbo.Karlsson

Note that in the call to sp_rename, you should not include the schema for the new name.