SELECT INTO TEMP - Create Temp Table - Informix to SQL Server Migration

In Informix, you can use the SELECT INTO TEMP statement to create a temporary table based on the query results.

In SQL Server, you can also use the SELECT INTO statement, but the position of the INTO clause is different and the temporary table name must start with the # sign.

Consider the following sample table:

  CREATE TABLE colors (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green');
  INSERT INTO colors VALUES ('Black');
  INSERT INTO colors VALUES ('Red');

Now let's create a temporary table using a query:

Informix:

  -- Create a new temporary table
  SELECT 'A' AS c1, name
  FROM colors
  WHERE name = 'Green'
  INTO TEMP tmp1;
  /* 1 row(s) retrieved into temp table. */
 
  -- Query the temporary table
  SELECT * FROM tmp1;
  /* A   Green */

SQL Server:

  -- Create a new temporary table
  SELECT 'A' AS c1, name
  INTO #tmp1
  FROM colors
  WHERE name = 'Green';
  /* (1 row affected) */
 
  -- Query the temporary table
  SELECT * FROM #tmp1;
  /* A   Green */

For more information, see Informix to SQL Server Migration.