I was trying to find the quickest way to export both structure and data from all tables of a database from Navicat Premium and came up with these steps:
* Use the backup tool to create a backup of your database
* Right click the backup and select to Extract SQL…
* Save to wherever you like
You can now use that .sql file to create a new database wherever you like.
If you have a lot of data in a mysql table and you’d like to duplicate some of the rows – maybe with a change or two as well – then here is the sql you’ll need…
INSERT INTO table (column1, column2,column3, column4) SELECT column1, 234, column4, NOW() FROM table WHERE id IN (6,7)
You have some choices…
You can copy or clone from one table to another by changing the table names above.
You can define your own values. Examples of 234 and NOW() are above.
You can include or drop the WHERE clause to choose which (if not all) rows to clone
Some database management tools refuse to admit you can add multiple primary key columns to one table, so you might have to do it with direct sql syntax ( deep intake of breath ).
Multiple primary keys make ‘ON DUPLICATE KEY UPDATE column = column + 100′ type queries a shed load more flexible.
Anyway, on with the code:
ALTER TABLE tablename ADD PRIMARY KEY (column_one, column_two)
And there you have it. Robert’s your fathers brother.
Occasionally working with SQL databases (not my beloved MySQL) I always struggled getting the number of rows in a consistant manner.
Until now…