Skip to content

Instantly share code, notes, and snippets.

@apop880
Created December 22, 2021 05:55
Show Gist options
  • Save apop880/2d8a781c3776e83d0152619a60b23b3f to your computer and use it in GitHub Desktop.
Save apop880/2d8a781c3776e83d0152619a60b23b3f to your computer and use it in GitHub Desktop.
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment