For Teradata
For Oracle
FOR SQL SERVER
Introduction
SnowConvert for SQL Server introduction page
What is SnowConvert for SQL Server?
SnowConvert is a software that understands SQL Server scripts and converts this source code into functionally equivalent Snowflake code.
Conversion Types
Specifically, SnowConvert for SQL Server performs the following conversions:
SQL Server to Snowflake SQL
SnowConvert understands the SQL Server source code and converts the Data Definition Language (DDL), Data Manipulation Language (DML), and functions in the source code to the corresponding SQL in the target: Snowflake.
Sample code
SQL Server basic input code:
1
CREATE TABLE Persons (
2
PersonID int,
3
LastName varchar(255),
4
FirstName varchar(255),
5
Address varchar(255),
6
City varchar(255)
7
);
Copied!
Snowflake SQL output code:
1
CREATE OR REPLACE TABLE PUBLIC.Persons (
2
PersonID INT,
3
LastName VARCHAR(255),
4
FirstName VARCHAR(255),
5
Address VARCHAR(255),
6
City VARCHAR(255)
7
);
Copied!
As you can see, most of the structure remains the same. There are some cases where the datatypes have to be transformed, for example.
SQL Server Stored Procedures to JavaScript Embedded in Snowflake SQL
SnowConvert takes SQL Server stored procedures and converts them to JavaScript embedded into Snowflake SQL. SQL Server's CREATE PROCEDURE is replaced by Snowflake's CREATE OR REPLACE PROCEDURE. JavaScript is called as a scripting language, and all of the inner statements are converted to JavaScript.
Sample code
SQL Server basic stored procedure:
1
CREATE PROCEDURE SelectAllCustomers
2
AS
3
SELECT * FROM Customers
4
GO;
Copied!
Snowflake SQL output code, with embedded JavaScript:
1
CREATE OR REPLACE PROCEDURE SelectAllCustomers ()
2
RETURNS STRING
3
LANGUAGE JAVASCRIPT
4
EXECUTE AS CALLER
5
AS
6
$
7
// REGION SnowConvert Helpers Code
8
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name;
9
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
10
var fixBind = function (arg) {
11
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
12
return arg;
13
};
14
var EXEC = (stmt,binds = [],noCatch = false) => {
15
binds = binds ? binds.map(fixBind) : binds;
16
for(var stmt of stmt.split(";").filter((_) => _)) {
17
try {
18
_RS = snowflake.createStatement({
19
sqlText : stmt,
20
binds : binds
21
});
22
_ROWS = _RS.execute();
23
ROW_COUNT = _RS.getRowCount();
24
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
25
return {
26
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
27
};
28
} catch(error) {
29
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
30
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
31
MESSAGE_TEXT = error.message.toString();
32
SQLCODE = error.code.toString();
33
SQLSTATE = error.state.toString();
34
snowflake.execute({
35
sqlText : `SELECT UPDATE_ERROR_VARS(?,?,?,?,?)`,
36
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME]
37
});
38
throw error;
39
}
40
}
41
};
42
// END REGION
43
​
44
EXEC(`SELECT * FROM PUBLIC.Customers`);
45
$;
46
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
47
--GO
48
​
49
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
50
--;
Copied!
- When creating the JavaScript code, there is a portion of code added as a helper, required for an easier transformation of the contents of the procedure.
- You can expect to see warnings with an associated code to help you find out what is happening in the converted code. (See issues and troubleshooting)
SnowConvert Terminology
Before we get lost in the magic of these code conversions, here are a few terms/definitions so you know what we mean when we start dropping them all over the documentation:
- SQL (Structured Query Language): the standard language for storing, manipulating, and retrieving data in most modern database architectures.
- SnowConvert: the software that converts securely and automatically your SQL Server files to the Snowflake cloud data platform.
- Conversion rule or transformation rule: rules that allow SnowConvert to convert from a portion of source code to the expected target code.
- Parse: parse or parsing is an initial process done by SnowConvert to understand the source code and build up an internal data structure required for executing the conversion rules.
On the next few pages, you'll learn more about the kind of conversions that SnowConvert for SQL Server is capable of. If you're ready to get started, visit the Getting Started page in this documentation.
Last modified 7mo ago