Snowflake Administration

Script to clone tables in snowflake

Run this script in sql server to generated the output command: snowflake table clone script.

SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @TABLE_SCHEMA varchar(max);
Declare @TABLE_NAME varchar(max);
DECLARE @command nvarchar(4000);

DECLARE TABLE_SCHEMA CURSOR FOR SELECT DISTINCT [TABLE_SCHEMA] FROM [clone].[dbo].[table_curated] where TABLE_SCHEMA <>'INFORMATION_SCHEMA' ORDER BY TABLE_SCHEMA;

open TABLE_SCHEMA;
fetch next from TABLE_SCHEMA into @TABLE_SCHEMA;
while @@FETCH_STATUS = 0
Begin
	DECLARE TABLE_NAME CURSOR FOR SELECT [TABLE_NAME] FROM [clone].[dbo].[table_curated] WHERE TABLE_SCHEMA=@TABLE_SCHEMA ORDER BY TABLE_NAME;
	open TABLE_NAME;
	fetch next from TABLE_NAME into @TABLE_NAME;
	while @@FETCH_STATUS = 0
	BEGIN
		Set @command = N'create or replace table PRE_PROD_CURATED.'+@TABLE_SCHEMA+'.'+@TABLE_NAME+' clone PROD_CURATED.'+@TABLE_SCHEMA+'.'+@TABLE_NAME+';';
		print @command
		--execute (@command);
		fetch next from TABLE_NAME into @TABLE_NAME
	END
	CLOSE TABLE_NAME;
	DEALLOCATE TABLE_NAME;
	fetch next from TABLE_SCHEMA into @TABLE_SCHEMA;
END
close TABLE_SCHEMA;
deallocate TABLE_SCHEMA;