Keywords: MySQL | CREATE TABLE SELECT | SELECT INTO | database migration
Abstract: This article explains how to use the CREATE TABLE SELECT syntax in MySQL as an alternative to SELECT INTO when migrating from MSSQL, covering key syntax, error analysis, comparisons, and practical recommendations.
During database migration, syntax compatibility is a major challenge. Users migrating from Microsoft SQL Server to MySQL often encounter issues with the SELECT INTO statement, leading to errors such as <code>Undeclared variable new_tbl</code>.
CREATE TABLE SELECT Syntax in MySQL
MySQL does not support the standard SELECT INTO syntax for creating new tables. Instead, it provides the CREATE TABLE SELECT syntax, which allows creating a table and inserting data simultaneously.
CREATE TABLE new_tbl SELECT * FROM orig_tbl;
This statement creates a new table named new_tbl and copies all data from orig_tbl into it. It combines table definition and data insertion, simplifying the migration process.
Error Analysis
In MySQL, attempting to use <code>SELECT INTO new_tbl FROM tbl;</code> results in an error because MySQL interprets new_tbl as a variable, not a table name. This differs from MSSQL, where SELECT INTO directly creates a table.
Comparison with Other Syntaxes
Besides CREATE TABLE SELECT, MySQL supports the INSERT INTO ... SELECT syntax for inserting data into existing tables. For example:
INSERT INTO this_table_archive (col1, col2, ..., coln) SELECT col1, col2, ..., coln FROM this_table WHERE entry_date < '2011-01-01 00:00:00';
This is different from CREATE TABLE SELECT, which creates a new table and inserts data, whereas INSERT INTO ... SELECT is used for appending data to an existing table.
Practical Recommendations
When migrating databases, it is recommended to use CREATE TABLE SELECT as a replacement for SELECT INTO in MSSQL. Ensure to check compatibility of data types and constraints to avoid data loss or inconsistency. For complex queries, test with SELECT statements first before applying CREATE TABLE SELECT.
In conclusion, MySQL offers flexible table creation and data copying through CREATE TABLE SELECT, effectively addressing the compatibility issues with SELECT INTO and enhancing migration efficiency.