SQLines provides tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server to Amazon Redshift.
Databases:
Microsoft
SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000
Amazon Redshift
Converting data types from SQL Server to Amazon Redshift:
| SQL Server | Redshift |
1 | BIGINT | 64-bit integer | BIGINT |
2 | BINARY(n) | Fixed-length byte string, 1 ⇐ n ⇐ 8000 | - |
3 | BIT | 1, 0 or NULL | SMALLINT |
4 | CHAR(n) | Fixed-length string, 1 ⇐ n ⇐ 8000 | CHAR(n) |
5 | DATE | Date (year, month and day) | DATE |
6 | DATETIME | Date and time with fraction | TIMESTAMP |
7 | DATETIME2(p) | Date and time with fraction | TIMESTAMP |
8 | DATETIMEOFFSET(p) | Date and time with fraction and time zone | TIMESTAMPTZ |
9 | DECIMAL(p,s) | Fixed-point number | DECIMAL(p,s) |
10 | DOUBLE PRECISION | Double-precision floating-point number | DOUBLE PRECISION |
11 | FLOAT(p) | Floating-point number | FLOAT |
12 | IMAGE | Variable-length binary data, ⇐ 2G | - |
13 | INT, INTEGER | 32-bit integer | INT, INTEGER |
14 | MONEY | 64-bit currency amount | DECIMAL(15,4) |
15 | NCHAR(n) | Fixed-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000 | - |
16 | NTEXT | Variable-length Unicode UCS-2 data, ⇐ 2G | LONGTEXT |
17 | NUMERIC(p,s) | Fixed-point number | NUMERIC(p,s) |
18 | NVARCHAR(n) | Variable-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000 | - |
19 | NVARCHAR(max) | Variable-length Unicode UCS-2 data, ⇐ 2G | - |
20 | REAL | Single-precision floating-point number | REAL |
21 | ROWVERSION | Automatically updated binary data | - |
22 | SMALLDATETIME | Datetime (year, month, day, hour, minutes
and 00 seconds ) | TIMESTAMP |
23 | SMALLINT | 16-bit integer | SMALLINT |
24 | SMALLMONEY | 32-bit currency amount | DECIMAL(6,4) |
25 | TEXT | Variable-length character data, ⇐ 2G | - |
26 | TIME(p) | Time (Hour, minute, second and fraction) | - |
27 | TIMESTAMP | Automatically updated binary data | - |
28 | TINYINT | 8-bit unsigned integer, 0 to 255 | SMALLINT |
29 | UNIQUEIDENTIFIER | 16-byte GUID (UUID) data | CHAR(16) |
30 | VARBINARY(n) | Variable-length byte string, 1 ⇐ n ⇐ 8000 | - |
31 | VARBINARY(max) | Variable-length binary data, ⇐ 2G | - |
32 | VARCHAR(n) | Variable-length character string, 1 ⇐ n ⇐ 8000 | VARCHAR(n) |
33 | VARCHAR(max) | Variable-length character data, ⇐ 2G | TEXT | ⇐ 256 |
34 | XML | XML data, ⇐ 2G | - |
Converting datetime functions:
| SQL Server | Redshift |
1 | EOMONTH(datetime) | Get the last day of the month of datetime | LAST_DAY(datetime) |
Converting CREATE TABLE statement keywords and clauses from SQL Server to Amazon Redshift:
| SQL Server | Redshift |
1 | IDENTITY(start, increment) | Identity column | IDENTITY(start, increment) |
2 | CONSTRAINT name PRIMARY | UNIQUE … | Constraint name | PRIMARY | UNIQUE … |
3 | PRIMARY KEY CLUSTERED | Primary key Constraint | PRIMARY KEY |
4 | CHECK bool_expr | CHECK Constraint | Not supported by Redshift, commented |
Primary, unique and foreign key constraints are informational only in Amazon Redshift.