Search code examples
mysqlt-sqlcode-translation

SQL Script converted to MySQL

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 ?

Solution

  • 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.