How to move a table from one schema to another Schema

In ETL, one of the strategy load data to Staging schema and then move to Live schema.

The following example shows that how to move a table from one schema to another schema.

CREATE SCHEMA Staging;

CREATE SCHEMA Live;

CREATE SCHEMA Tmp;

CREATE TABLE Staging.Department

(

   DepartmentNumber char(10) NOT NULL PRIMARY KEY CLUSTERED,

   DepartmentName varchar(50) NOT NULL,

   ManagerID INT  NULL,

   ParentDepartmentNumber char(10) NULL

);

CREATE TABLE Live.Department

(

   DepartmentNumber char(10) NOT NULL PRIMARY KEY CLUSTERED,

   DepartmentName varchar(50) NOT NULL,

   ManagerID INT  NULL,

   ParentDepartmentNumber char(10) NULL

);

--Switch Live to Tmp

ALTER SCHEMA Tmp TRANSFER Live.Department;

--Switch Staging to Live

ALTER SCHEMA Live TRANSFER Staging.Department;

--Switch Tmp to Staging

ALTER SCHEMA Staging TRANSFER Tmp.Department;

To get rid of risk, we can use this as SP with transaction.

Sample stored procedure for Switch schema with transaction.

CREATE PROCEDURE dbo.SwitchSchema

AS

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

--Switch Live to Tmp

ALTER SCHEMA Tmp TRANSFER Live.Department;

--Switch Staging to Live

ALTER SCHEMA Live TRANSFER Staging.Department;

--Switch Tmp to Staging

ALTER SCHEMA Staging TRANSFER Tmp.Department;

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

       ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0

   COMMIT TRANSACTION;

END;

Cheers!
Uma