-- create original source data
create table one(a int not null);
insert one values(1);

-- synonym for source
create synonym source for one;

-- only select from synonym
select * from source;

-- load staging
create table stage(a int not null);
insert stage values(2);

-- swap sources via synonym
begin try
	begin tran;
		drop synonym source;
		create synonym source for stage;
	commit tran;
end try
begin catch;
	rollback tran;
	raiserror('Synonym swap failed',16,1)
end catch

select * from source;

-- clean up
drop synonym source;
drop table one,stage;

-- schema swap
GO
-- create schema for staging
create schema stage authorization dbo;
GO
-- create holding schema for swap
create schema holding authorization dbo;
GO
--create tables and populate
create table dbo.source(a int not null)
create table stage.source(a int not null)
insert dbo.source values(1);
insert stage.source values(2);

-- make sure no holding table exists
if OBJECT_ID('holding.source') is not null drop table holding.source;

-- always select from dbo
select * from dbo.source;

-- swap data (dbo->holding, stage->dbo, holding->stage)
begin try
	begin tran;
		alter schema holding transfer dbo.source;
		alter schema dbo transfer stage.source
		alter schema stage transfer holding.source;
	commit tran;
end try
begin catch;
	rollback tran;
	raiserror('Schema swap failed',16,1)
end catch

select * from dbo.source;

-- clean up 
drop table stage.source, dbo.source;
if OBJECT_ID('holding.source') is not null drop table holding.source;