top of page

Snowflake Create Sequence - it uses two cursors one for schema name and another for table name

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

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

open SEQUENCE_SCHEMA;
fetch next from SEQUENCE_SCHEMA into @SEQUENCE_SCHEMA;
while @@FETCH_STATUS = 0
Begin
	DECLARE SEQUENCE_NAME CURSOR FOR SELECT [SEQUENCE_NAME] FROM [clone].						[dbo].[table_seq_curated] WHERE SEQUENCE_SCHEMA=@SEQUENCE_SCHEMA ORDER BY SEQUENCE_NAME;
	open SEQUENCE_NAME;
	fetch next from SEQUENCE_NAME into @SEQUENCE_NAME;
	while @@FETCH_STATUS = 0
	BEGIN
		Set @command = N'create or replace sequence PRE_PROD_CURATED.'+@SEQUENCE_SCHEMA+'.'+@SEQUENCE_NAME+' start with 10502977 increment by 1;';
		print @command
		--execute (@command);
		fetch next from SEQUENCE_NAME into @SEQUENCE_NAME
	END
	CLOSE SEQUENCE_NAME;
	DEALLOCATE SEQUENCE_NAME;
	fetch next from SEQUENCE_SCHEMA into @SEQUENCE_SCHEMA;
END
close SEQUENCE_SCHEMA;
deallocate SEQUENCE_SCHEMA;
2 views0 comments

Recent Posts

See All

Comentários


bottom of page