This is an old revision of the document!


Informix to SQL Server Migration

SQLines provides open source tools 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 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

Converting 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 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 32767 CHAR(n), CHARACTER(n)
7 CHARACTER VARYING(n,r) Variable-length string, 1 ⇐ n ⇐ 255 CHARACTER VARYING(n)
8 CLOB Character large object, ⇐ 4T VARCHAR(max)
9 DATE Date (year, month and day) DATE
10 DATETIME unit TO unit2 Date and time with fraction DATETIME2
11 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
12 DOUBLE PRECISION Double-precision floating-point number FLOAT
13 FLOAT(p) Double-precision floating-point number FLOAT
14 INTEGER, INT 32-bit integer INTEGER, INT
15 INT8 64-bit integer BIGINT
16 INTERVAL unit TO unit2 Date and time interval VARCHAR(30)
17 LVARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32739 VARCHAR(n)
18 MONEY(p,s) Currency amount MONEY
19 NCHAR(n) Fixed-length string, 1 ⇐ n ⇐ 32767 NCHAR(n)
20 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
21 NVARCHAR(n,r) Variable-length string, 1 ⇐ n ⇐ 255 NVARCHAR(n)
22 REAL Single-precision floating-point number REAL
23 SMALLFLOAT Single-precision floating-point number REAL
24 SMALLINT 16-bit integer SMALLINT
25 SERIAL(s) Auto-increment 32-bit integer INT
26 SERIAL8(s) Auto-increment 64-bit integer BIGINT
27 TEXT Character data, ⇐ 2G VARCHAR(max)
28 VARCHAR(n,r) Variable-length string, 1 ⇐ n ⇐ 255 VARCHAR(n)

Data type attributes and options

Informix SQL Server
1 column BYTE IN TABLE column VARBIANRY(max)
2 column BYTE IN lob_space column VARBIANRY(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