SELECT INTO - Creating Temporary Table - SQL Server to MariaDB Migration

In SQL Server you can use a SELECT INTO statement to create a temporary table based on the query results.

In MariaDB you have to use CREATE TEMPORARY TABLE AS SELECT statement.

SQL Server:

  -- Create a new temporary table
  SELECT 'A' AS c1 INTO #tmp;
 
  -- Query the temporary table
  SELECT * FROM #tmp;
  # A

MariaDB:

  -- Create a new temporary table
  CREATE TEMPORARY TABLE tmp AS SELECT 'A' AS c1;
 
  -- Query the temporary table
  SELECT * FROM tmp;
  # A

Create Temporary Table in Procedure

SQL Server allows you to use the same SELECT INTO statement to create a temporary table in Transact-SQL procedure:

SQL Server:

  CREATE PROCEDURE sp_create_temp
  AS
    SELECT 'A' AS c1 INTO #tmp2;
  GO

You can use CREATE TEMPORARY TABLE AS SELECT in MariaDB procedure:

MariaDB:

  DELIMITER //
 
  CREATE PROCEDURE sp_create_temp()
  BEGIN
    CREATE TEMPORARY TABLE tmp2 AS SELECT 'A' AS c1;
  END;
  //
 
  DELIMITER ;