A sample stored procedure for moving data from Snowflake to SQL Server using Snowflake as a linked server. This is a companion for my blog post at https://apop.tech/posts/moving-data-from-snowflake-to-sql-server

COPY_FROM_SNOWFLAKE.sql

USE [STAGE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Alex Popoutsis
-- Create date: 11/2/2021
-- Description:	Copy tables from Snowflake
-- =============================================
ALTER PROCEDURE [DBO].[COPY_FROM_SNOWFLAKE] 
AS
BEGIN

SET NOCOUNT ON

DECLARE @TBL VARCHAR(8000)
DECLARE @COL VARCHAR(8000)
DECLARE @DATA_TYPE VARCHAR(8000)
DECLARE @FIRST_ROW BIT
DECLARE @SQL VARCHAR(MAX)
DECLARE @SQL_CREATE_TABLE VARCHAR(MAX)
DECLARE @SQL_FOR_COLS NVARCHAR(MAX)

--CLEAR AND FILL RELATIONSHIPS TABLE
DROP TABLE IF EXISTS SOURCE.RELATIONSHIPS

SELECT *
INTO SOURCE.RELATIONSHIPS
FROM OPENQUERY(SNOWFLAKE,
'SELECT DISTINCT NODE_1_ID::VARCHAR(8000) AS NODE_1_ID,
NODE_2_ID::VARCHAR(8000) AS NODE_2_ID,
NODE_1_LABEL::VARCHAR(8000) AS NODE_1_LABEL,
NODE_2_LABEL::VARCHAR(8000) AS NODE_2_LABEL,
METADATA::VARCHAR(8000) AS METADATA
FROM SOURCE.SOURCE.RELATIONSHIPS')

DROP TABLE IF EXISTS #MYTEMP

--GET LIST OF TABLES TO COPY
SELECT TBLS INTO #MYTEMP FROM OPENQUERY(SNOWFLAKE,
'SELECT DISTINCT NODE_1_LABEL::VARCHAR(8000) AS TBLS
FROM SOURCE.SOURCE.RELATIONSHIPS')

SET ROWCOUNT 1
SELECT @TBL = TBLS FROM #MYTEMP

WHILE @@ROWCOUNT <> 0
BEGIN
	SET ROWCOUNT 0
	--DROP THE TABLE
	SET @SQL = 'DROP TABLE IF EXISTS SOURCE.['+@TBL+']'
	EXEC(@SQL)

	--BUILD THE DYNAMIC SQL TO EXTRACT DATA
	--PREPEND THE QUERY WITH THE DESTINATION TABLE
	SET @SQL = CAST('INSERT INTO SOURCE.[' AS VARCHAR(MAX))+@TBL+'] EXECUTE(''SELECT '

	--BUILD THE SQL TO CREATE TABLE
	SET @SQL_CREATE_TABLE = CAST('CREATE TABLE SOURCE.[' AS VARCHAR(MAX))+@TBL+'] ('

	--GET THE COLUMNS FROM SNOWFLAKE AND DYNAMICALLY CONSTRUCT SELECT STATEMENT WITH VARCHAR CASTS
	DROP TABLE IF EXISTS ##MYTEMPCOLS
	SET @SQL_FOR_COLS = '
	SELECT * INTO ##MYTEMPCOLS FROM
	OPENQUERY(SNOWFLAKE, ''SELECT COLUMN_NAME::VARCHAR(8000) AS COLUMN_NAME, DATA_TYPE::VARCHAR(8000) AS DATA_TYPE FROM SOURCE.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = UPPER('''''+@TBL+''''')'')'

	EXECUTE SP_EXECUTESQL @SQL_FOR_COLS
	
	SET @FIRST_ROW = 1
	SET ROWCOUNT 1
	SELECT @COL = COLUMN_NAME, @DATA_TYPE = DATA_TYPE FROM ##MYTEMPCOLS

	WHILE @@ROWCOUNT <> 0
	BEGIN
	SET ROWCOUNT 0

		--PRINT(@COL)
		
		IF (@FIRST_ROW = 0)
		BEGIN
			SET @SQL += '
			,'
			SET @SQL_CREATE_TABLE += '
			,'
		END

		SET @SQL += @COL

		IF (@DATA_TYPE = 'TEXT')
		BEGIN
			SET @SQL += '::VARCHAR(1000) AS '+@COL
			SET @SQL_CREATE_TABLE += @COL + ' VARCHAR(1000)'
		END
		IF (@DATA_TYPE = 'FLOAT')
			SET @SQL_CREATE_TABLE += @COL + ' FLOAT'
		IF (@DATA_TYPE = 'NUMBER')
			SET @SQL_CREATE_TABLE += @COL + ' NUMERIC(38,0)'
		IF (@DATA_TYPE = 'DATE')
			SET @SQL_CREATE_TABLE += @COL + ' DATE'
		IF (@DATA_TYPE = 'BOOLEAN')
			SET @SQL_CREATE_TABLE += @COL + ' BIT'

	DELETE ##MYTEMPCOLS WHERE COLUMN_NAME = @COL
	SET @FIRST_ROW = 0
	SET ROWCOUNT 1
	SELECT @COL = COLUMN_NAME, @DATA_TYPE = DATA_TYPE FROM ##MYTEMPCOLS	
	END
	SET ROWCOUNT 0

	--APPEND THE LINKED SERVER TO CLOSE THE QUERY
	SET @SQL += ' FROM SOURCE.'+@TBL+''') AT SNOWFLAKE'

	--CLOSE TABLE CREATION STATEMENT
	SET @SQL_CREATE_TABLE += ')'
	
	--EXECUTE THE SQL
	--PRINT CAST(@SQL_CREATE_TABLE AS NTEXT)
	EXEC(@SQL_CREATE_TABLE)

	--PRINT CAST(@SQL AS NTEXT)
	EXEC(@SQL)

DELETE #MYTEMP WHERE TBLS = @TBL
SET ROWCOUNT 1
SELECT @TBL = TBLS FROM #MYTEMP

END
SET ROWCOUNT 0

END