Informix to SQL Server Migration

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

SQLines SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

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

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)
4	LENGTH(string)	Get string length	LEN(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
2	PRIMARY KEY (c1, c2, …) CONSTRAINT schema.name	CONSTRAINT name PRIMARY KEY (c1, c2, …)	No schema

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 …
2	OUTER clause	Outer join syntax	ANSI SQL OUTER JOIN clause

GROUP BY clause:

	Informix		SQL Server
1	SELECT c1, c2, … FROM t GROUP BY 1, 2	Positional reference	SELECT c1, c2, … FROM t GROUP BY c1, c2

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
4	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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference