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
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 and constructs:

	Informix		SQL Server
1	Single '' and double "" quotes	Identifiers and literals	Single '' and double "" quotes
2	{ comment }		/* comment */
3	string[start, end]	Substring operator []	SUBSTRING(string, start, end - start + 1)
3	string[start]	Substring operator []	SUBSTRING(string, start, 1)
4	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)

Numeric data types:

	Informix		SQL Server
1	BIGINT	64-bit integer	BIGINT
2	BIGSERIAL(s)	Auto-increment 64-bit integer	BIGINT
3	DECIMAL(p,s)	Fixed-point number	DECIMAL(p,s)
4	DOUBLE PRECISION	Synonym for FLOAT	FLOAT
5	FLOAT	Double-precision floating-point number	FLOAT
6	INTEGER, INT	32-bit integer	INTEGER, INT
7	INT8	64-bit integer	BIGINT
8	MONEY(p,s)	Currency amount	MONEY
9	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
10	REAL	Synonym for SMALLFLOAT	REAL
11	SMALLFLOAT	Single-precision floating-point number	REAL
12	SMALLINT	16-bit integer	SMALLINT
13	SERIAL(s)	Auto-increment 32-bit integer	INT
14	SERIAL8(s)	Auto-increment 64-bit integer	BIGINT

Other data types:

	Informix		SQL Server
1	BLOB	Binary large object, ⇐ 4T	VARBINARY(max)
2	BOOLEAN	True, false or NULL	BIT
3	BYTE	Binary data, ⇐ 2G	VARBINARY(max)
4	CLOB	Character large object, ⇐ 4T	VARCHAR(max)
5	DATE	Date (year, month and day)	DATE
6	DATETIME unit TO unit2	Date and time with fraction	DATETIME2
7	DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
8	INTERVAL unit TO unit2	Date and time interval	VARCHAR(30)
9	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 date and time functions:

	Informix		SQL Server
1	CURRENT	Get the current date and time	GETDATE()
2	DAY(datetime)	Get the day of datetime	DAY(datetime)
3	MONTH(datetime)	Get the month (1-12) of datetime	MONTH(datetime)
4	TODAY	Get the current date (year, month and day)	CONVERT(DATE, GETDATE())

Converting other functions:

	Informix		SQL Server
1	DBINFO('sqlca.sqlerrd2')	Get the number of affected rows	@@ROWCOUNT
2	DECODE(exp, when, then, …, else)	Evaluate conditions	CASE exp WHEN when THEN then … ELSE else END
3	LEN(string)	Get string length	LEN(string)
3	LENGTH(string)	Get string length	LEN(string)
4	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

Create table from query:

	Informix		SQL Server
1	SELECT ... FROM ... INTO TEMP tab	Create a temporary table	SELECT ... INTO #tab FROM ...

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 FUNCTION Statement

Converting user-defined functions:

	Informix		SQL Server
1	CREATE FUNCTION name		CREATE FUNCTION name
2	END FUNCTION	End of function block	END GO
3	DOCUMENT "notes", …	User notes for procedure	Commented
4	WITH LISTING IN 'file'	Warnings file	Removed

For more information, see Conversion of Procedural Statements.

CREATE PROCEDURE Statement

Converting stored procedures:

	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
7	DOCUMENT "notes", …	User notes for procedure	Commented
8	WITH LISTING IN 'file'	Warnings file	Removed

For more information, see Conversion of Procedural Statements.

Procedural SQL Statements

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 SELECT ... END FOREACH	Query loop	DECLARE, OPEN, WHILE, FETCH and CLOSE
2	FOR i = n TO k ... END FOR	Range loop	WHILE @i <= k BEGIN ... END
3	EXIT FOR	Exit loop	BREAK
4	IF cond THEN … ELIF … ELSE … END IF	IF statement	IF cond BEGIN … END ELSE IF … ELSE …

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