Tag Archives: duplicate

How to Duplicate a Table in FlameRobin CodeUnit 03 OCT 2011

FlameRobin is a lightweight, cross-platform database administration tool for Firebird databases. It is also NOT one of my favourite tools to use because of its very lightweight nature! Nevertheless, there are times I must use it, and use it I shall, and besides, other people seem to like it, so it’s worth a post or two. Today I’ll quickly jot down a note on how to duplicate a table within FlameRobin.

First, connect to the database instance by selecting it from the explorer window and double clicking on it. Once the table list is loaded, select the table that you wish to duplicate. Click on it to launch a new information window which happens to have the following headers at the top: Summary, Constraints, Triggers, Indices, Privileges, Dependencies and DDL.

Click on the DDL link.

Copy the resulting SQL statement.

Now right click on the Tables menu option in the main FlameRobin window and select the “Create new…” menu option.

Paste the copied SQL into the window, change the table name sections to the duplicate’s desired name, and click on the Run (Play icon) button. Pressing F4 will also cause the statement to execute. If you’re happy with the report, click on the green tick button to commit your transaction, and smile as the table list in the main window magically gets updated with your new table!

Nifty.

SQL: Finding Duplicate Rows based on Multiple Columns CodeUnit 25 JUN 2010

Locating duplicate rows based on multiple column values with SQL turns out to be a fairly simple exercise.

If we take the usual method for locating duplicate rows based on a single column’s value, we see that locating a duplicate is nothing more than grouping on that column where the instance of that column value appears more than once.

In other words:

SELECT email, COUNT(email)
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Now to find duplicate rows based on the values held by multiple columns, we simply expand on what we have done before and provide more parameters to work against, resulting in a SQL statement that looks like:

SELECT firstname, COUNT(firstname), surname, COUNT(surname), email, COUNT(email) 
FROM users
GROUP BY firstname, surname, email
HAVING (COUNT(firstname) > 1) AND (COUNT(surname) > 1) AND (COUNT(email) > 1)

And that is seriously how simple finding duplicate rows based on a compound key (multiple columns restraint) really is! :)

SQL: Finding all Duplicate Records in a Table CodeUnit 03 MAR 2010

Sometimes it is quite handy to see which records based on a particular key appear more than once in a database. The trick here is that we are focusing on a particular column and deeming that should that particular key appear more than once in the table  then we have a duplicate scenario.

So let us look at constructing the necessary SQL statement.

For our example, let’s use a table called emails_sent which contains a whole lot of various columns including email_address, which will act as our key to search upon. So wanting to find all complete records for all email_addresses that appear more than once in the table, we put down:

SELECT *
FROM emails_sent
WHERE email_address IN (
SELECT email_address
FROM emails_sent
GROUP BY email_address
HAVING (COUNT(email_address) > 1)
)

Breaking this down, we’ll see that the SQL statement is actually made up of two distinct parts. The first part is the meaty one that is responsible for grabbing all the duplicate records, while the second part simply uses the result set stemming from this first segment to then return all the column values for all the duplicate records. Now while the second part is fairly simple, just a SELECT *FROM x WHERE key IN statement, the first segment deserves a bit more of an explanation.

What we are doing is grouping all email_addresses together that look the same using the GROUP BY clause. Next, we are applying a HAVING condition to the GROUP BY clause, this time asking that the only groups to keep are those for which the count of the group key is greater than 1. At this point the light should be blinking on and you should be shouting out, “Ah ha, so that is how it works”  in case you are wondering.

So the first part returns all the duplicate record keys and the second part then grabs all information for records that correspond on those initially returned duplicate keys.

Simple really.

We could of course through a simple modification to our HAVING clause change the SQL that it only returns records where the key only appears once – i.e. change HAVING (COUNT(email_address) > 1 to HAVING (COUNT(email_address) = 1.

To return the duplicate keys and the number of times that they appear we could use this:

SELECT email_addresses, count(email_address) AS numOccurrences
FROM emails_sent
GROUP BY email_address
HAVING (COUNT(email_address) > 1)

MySQL: How to Duplicate a Table Tips, Tricks and Tutorials 10 SEP 2009

There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when you want to create a backup of an existing table. In MySQL however this doesn’t work straight out of the box, which of course is a pain in the ass.

So how does one duplicate this functionality in MySQL then?

Well funnily enough, it’s actually pretty damn simple. The CREATE TABLE IF NOT EXISTS phrase is key here and combining this with a select statement will in fact create a copy of your existing table, taking all the data from your source table and dumping it into your newly created clone table. (Note that you can control what data gets copied into the new table by modifying the SELECT statement with an appropriate WHERE statement as well as the columns that get created by specifying column names in the first part of the SELECT statement in place of the asterisk).

So for example: “CREATE TABLE IF NOT EXISTS `my_backup_table` SELECT * FROM `my_table`”

…will create the `my_backup_table` if it doesn’t exist and copy over all data currently contained in `my_table`.

Pretty useful, no?

It is however important to note that this process does come with a few drawbacks. Firstly, attribute data like primary keys, comments, etc. gets lost in the process. Also, don’t expect any associated triggers to make the trip either. Finally, certain default values like CURRENT_TIMESTAMP gets converted to 0000-00-00 00:00:00 as well, just to add insult to injury.

But then, if this backup table really is just about keeping the existing data safe, then I guess this really shouldn’t matter all that much to you in the first place! :)

a nest of wooden tables