SQL Script converted to MySQL

Question

I am trying to fix this migration history problem but can't get this to work in MySQL

SELECT *
INTO [TempMigrationHistory]
FROM [__MigrationHistory]

DROP TABLE [__MigrationHistory]

EXEC sp_rename 'TempMigrationHistory', '__MigrationHistory'

How would I get this working in mySQL ?

score 0 · Answer 1 · answered Mar 10 '12 at 20:47

0

Not all RDBMS's behave the same. Have you considered searching for the MySQL syntax for creating an INSERT statement? E.g.,

INSERT INTO TempMigrationHistory
            (Col1,
             Col2,
             Col3)
SELECT Col1,
       Col2,
       Col3
FROM   __MigrationHistory

Also, sp_rename is a SQL Server Stored Procedure. An equivalent in MySQL is RENAME. In your case, this will probably work:

RENAME TABLE TempMigrationHistory TO __MigrationHistory;

answered Mar 10 '12 at 20:47

gangreen

849
7
9

thanks the prob is that that the tempmigrationhistory table doesnt exist - so I thought it would need to create an in memory table and then move it ? the issue is that using EF 4.3 - creates a system table that I need not to be a system table ? – Andy Mar 11 '12 at 04:11
I think you need to edit your question to address the specific issue you are encountering. As written, your question seems to ask how to insert records and rename a table. The system table issue came out of left field. – gangreen Mar 11 '12 at 04:53

score 0 · Accepted Answer · answered Mar 11 '12 at 14:19

A close equivalent mysql SQL for the SQL Server t-sql provided is something like what follows here. (Obviously field1, field2 are placeholders.)

CREATE TABLE `TempMigrationHistory` LIKE `__MigrationHistory`;

INSERT INTO `TempMigrationHistory` (field1, field2)
SELECT field1, field2
FROM `__MigrationHistory`;

DROP TABLE `__MigrationHistory`;

RENAME TABLE `TempMigrationHistory` to `__MigrationHistory`;

Points to note...

mysql does not support SELECT INTO but it does have the CREATE TABLE LIKE statement that SQL Server does not.
Backticks (`) are used by convention in mysql instead of square brackets ([) in t-sql.
mysql often has special system commands that take the place of SQL Server system stored procedure functionality (like sp_rename with RENAME TABLE in this case).

At the end of this sequence of operations it seems you have exactly what you started with so I'm a bit confused about the desire to execute this sequence of events, but I suppose that's a tangent to your question.

I thought there was also `CREATE TABLE t AS SELECT ...` in MySQL, which would be closer to T-SQL's `SELECT INTO` than `CREATE TABLE LIKE` + `INSERT ... SELECT`. — Andriy M, Mar 11 '12 at 15:09
@Adriy: good point about `CREATE TABLE ... SELECT`. It would be another alternative. — Tahbaza, Mar 11 '12 at 22:21

SQL Script converted to MySQL

2 Answers2