Informix to SQL Server Migration

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Informix to Microsoft SQL Server.

We also help convert embedded SQL statements in C/C++, C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Python, Perl/PHP and other applications.

  • Informix 12.x, 11.x, 10.x, 9.x and 7.x (Dynamic Server IDS and Extended Parallel Server XPS)
  • Microsoft SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and 2005

Informix to SQL Server Migration Tools

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from Informix to SQL Server.

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an Informix database use SQLines Data tool.

SQLines tool is available in Online and Desktop editions.

Try SQLines Online or download Desktop Version.

Informix to SQL Server Migration Reference

SQL Language Elements

Converting SQL language elements from Informix to SQL Server:

Informix SQL Server
1 { comment } /* comment */
2 string[start, end] Substring operator [] SUBSTRING(string, start, end - start + 1)
string[start] SUBSTRING(string, start, 1)
3 DBINFO('sqlca.sqlerrd2') Get the number of affected rows @@ROWCOUNT

Data Types

Character data types:

Informix SQL Server
1 CHAR(n), CHARACTER(n) Fixed-length string, 1 <= n <= 32767 CHAR(n), CHARACTER(n) n <= 8000
VARCHAR(max) n > 8000
2 CHARACTER VARYING(n,r) Variable-length string, 1 <= n <= 255 CHARACTER VARYING(n)
3 LVARCHAR(n) Variable-length string, 1 <= n <= 32739 VARCHAR(n)
4 NCHAR(n) Fixed-length string, 1 <= n <= 32767 NCHAR(n)
5 NVARCHAR(n,r) Variable-length string, 1 <= n <= 255 NVARCHAR(n)
6 VARCHAR(n,r) Variable-length string, 1 <= n <= 255 VARCHAR(n)

Other data types:

Informix SQL Server
1 BIGINT 64-bit integer BIGINT
2 BIGSERIAL(s) Auto-increment 64-bit integer BIGINT
3 BLOB Binary large object, ⇐ 4T VARBINARY(max)
4 BOOLEAN True, false or NULL BIT
5 BYTE Binary data, ⇐ 2G VARBINARY(max)
6 CLOB Character large object, ⇐ 4T VARCHAR(max)
7 DATE Date (year, month and day) DATE
8 DATETIME unit TO unit2 Date and time with fraction DATETIME2
9 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
10 DOUBLE PRECISION Double-precision floating-point number FLOAT
11 FLOAT(p) Double-precision floating-point number FLOAT
12 INTEGER, INT 32-bit integer INTEGER, INT
13 INT8 64-bit integer BIGINT
14 INTERVAL unit TO unit2 Date and time interval VARCHAR(30)
15 MONEY(p,s) Currency amount MONEY
16 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
17 REAL Single-precision floating-point number REAL
18 SMALLFLOAT Single-precision floating-point number REAL
19 SMALLINT 16-bit integer SMALLINT
20 SERIAL(s) Auto-increment 32-bit integer INT
21 SERIAL8(s) Auto-increment 64-bit integer BIGINT
22 TEXT Character data, ⇐ 2G VARCHAR(max)

Data type attributes and options:

Informix SQL Server
1 column BYTE IN TABLE column VARBINARY(max)
2 column BYTE IN lob_space column VARBINARY(max)
3 column TEXT IN TABLE column VARCHAR(max)
4 column TEXT IN lob_space column VARCHAR(max)

Built-in SQL Functions

Converting built-in SQL functions:

Informix SQL Server
1 CURRENT Get the current date and time GETDATE()
2 DBINFO('sqlca.sqlerrd2') Get the number of affected rows @@ROWCOUNT
3 DECODE(exp, when, then, …, else) Evaluate conditions CASE exp WHEN when THEN then
ELSE else END
4 LEN(string) Get string length LEN(string)
LENGTH(string)
5 TRIM(string) Remove leading and trailing spaces RTRIM(LTRIM(string))

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Informix SQL Server
1 Primary key columns are changed to NOT NULL NOT NULL constraint must be specified explicitly

SELECT Statement

Converting SQL queries from Informix to SQL Server:

Informix SQL Server
1 SELECT FIRST n Return n rows after sorting SELECT TOP n

CREATE PROCEDURE Statement

Converting stored procedures from Informix to SQL Server:

Informix SQL Server
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 name() When without parameters name
3 OUT | INOUT param datatype(len) DEFAULT default @param datatype(len) = default OUT
4 RETURNING datatype Scalar return value Converted to CREATE FUNCTION
RETURN WITH RESUME Multiple rows returned Converted to a table-valued function
5 No AS keyword before the statements block AS is added
6 END PROCEDURE; End of procedure block GO

For more information, see Conversion of Procedural Statements.

Procedural SQL Statements

Converting procedural SQL statements (SPL) used in stored procedures, functions and triggers from Informix to SQL Server.

Variable declaration and assignment:

Informix SQL Server
1 variable LIKE table.column Inherited data type @variable datatype
2 DEFINE var datatype(len) Variable declaration DECLARE @var datatype(len)
3 LET var = value; Assignment statement SET @var = value;
4 SELECT col INTO var FROM Select a single row SELECT @var = col FROM

Flow-of-control statements:

Informix SQL Server
1 FOREACH [cur FOR] select INTO vars
stmt END FOREACH
Query loop DECLARE cur CURSOR FOR select;
OPEN-WHILE-FETCH-CLOSE
2 IF condition THEN … END IF; IF statement IF condition BEGIN … END