06 December 2007

Creating Copies of Tables: MS SQL vs. MySql

I was reminded the other day how SQL Server was annoying in regards to table structure changes. All I wanted to to was copy a table (with structure intact - keys, dependancies) with its data.

How hard could that be?

MS SQL in Action

First, I needed to do a complete DDL on TableOne and replace every instance of the word "TableOne" with "TableTwo". It works, even though it is bulky and takes time, especially when the DDL was 68 lines long.

The second step was not too bad.
SELECT * FROM TableOne INTO TableTwo
Ok - that did not seem too hard, until you see how this can be accomplished in MySql.

MySql's Turn
CREATE TABLE table_1 LIKE table_2;
INSERT INTO table_1 SELECT * FROM table_2;
Which looks easier to you?

Here's another wrinkle: what about table-level permissions? Which side is easier to manage?

Until next time,

1 comment:

Keith said...

David, this is keith from the MUG meeting last night. I just pulled out the card to get your email address and it was smudged beyond recognition. You have my email .. send me one :)