[Larry Woods]

This should do it:

INSERT INTO table1 SELECT * FROM table2;

This would add the records from table2 into (append) table1 ASSUMING the same schema for both tables.

Larry Woods

'dirk wrote:

Hoi,

What is the easiest way to merge tables in the same database ?

Thanks

Dirk

[Larry Woods]

'dyanc' wrote:

This question is probably outside the realm of this forum but I was hoping that someone might take pity on me! We have a large database of media outlets with names, contact info, etc. In a “category” column, we enter the focus of each contact – News, Politics, Health, Family, etc. (info is entered using shorter forms, i.e. N, POL, FAM, HL). We have some cases where one person will fall into more than one category, and I've been told that I can enter more than one category with a comma between (i.e., N, POL, HL). Question: Is there a limit to the number of categories that could be separated by a comma?

It has been suggested that we set up a separate column for each category (about 20), and then place a “Y” in each column that is appropriate for the contact. I think it would be unwieldy to have to look at 20 columns of categories before I even get to see my other information. Does anyone have a suggestion as to how others may have set up a similar database?

My knowledge of SQL and databases is VERY basic – I know enough code to write the SELECT statements that I need! Thanks.

Dyanc:

First, if you are creating a column with a single category in it then you need to put a single category in it. Remember that as far as mySQL is concerned this is nothing but some text. If you choose to put multiple categories in a single field using commas to delete then you have created a string of characters. The point is that mySQL will NOT recognize this as being “multiple categories” but only as a string of text.

You could create multiple fields for categories but the “proper” way to do this is to create an additional table, say called “ownercategorylinks” that would have records that might look like “ownerID,category”. And, better yet, you should have a table of “categories” with a schema like “categoryID,category” and then the “ownercategorylinks” schema would look something like “ownerID,categoryID”. This way you can have unlimited owner/category combinations.

Hope this helps…

Larry Woods

[Larry Woods]

'Larry wrote:

While waiting for your answer I connected to a database and entered the following in the Query window:

SELECT * FROM table1 LIMIT 1;

SELECT * FROM table2 LIMIT 1

I tried just hitting Shift-F9 (AND Shift-F5) both with, and without the “;” at the end of the first SELECT statement. I also tried selecting the statements and NOT selecting the statements. The only action that produced any result was the one above (with the “;” at the end of the first SQL statement). By selecting the two SQL statements I got a result of ONE record; the record from table1. I did NOT receive any result from table2!

In snooping around I HAVE found the output from the second query under another “Result” tab. So I checked the documentation. Huh! Doesn't tell me that is where I should have found it! Other packages that I have used (MS Enterprise Manager) lists the results in a single “Results” window, which is what I was expecting since I wanted to compare results—which I would image most users would want to do.

[Larry Woods]

'Larry wrote:

Well, I DID check the documentation and I am using the Query window for a LOT of debugging, consequently I have about 30 queries in the window, including DELETEs and INSERTS. The documentation says that Shift-F9 (AND NOT Shift-F5) will execute ALL queries. I read that to mean ALL queries in the window and NOT just the selected queries. If Shift-F5 (Shift-F9) will execute JUST the SELECTED SQL statements then it might be a good idea to say that! So, before I destroy my database, can you tell me (1) is it Shift-F5 or Shift-F9 and (2) will it execute ALL queries or just the SELECTED queries?

Thanks

While waiting for your answer I connected to a database and entered the following in the Query window:

SELECT * FROM table1 LIMIT 1;

SELECT * FROM table2 LIMIT 1

I tried just hitting Shift-F9 (AND Shift-F5) both with, and without the “;” at the end of the first SELECT statement. I also tried selecting the statements and NOT selecting the statements. The only action that produced any result was the one above (with the “;” at the end of the first SQL statement). By selecting the two SQL statements I got a result of ONE record; the record from table1. I did NOT receive any result from table2!

[Larry Woods]

'peterlaursen' wrote:

Of course it is possibe. Click the 'double arrow icon (or use shortcut Shift+F5) to execute all queries. A RESULT tab will open for each query.

(maybe you should check the documentation from help .. help menu)

Well, I DID check the documentation and I am using the Query window for a LOT of debugging, consequently I have about 30 queries in the window, including DELETEs and INSERTS. The documentation says that Shift-F9 (AND NOT Shift-F5) will execute ALL queries. I read that to mean ALL queries in the window and NOT just the selected queries. If Shift-F5 (Shift-F9) will execute JUST the SELECTED SQL statements then it might be a good idea to say that! So, before I destroy my database, can you tell me (1) is it Shift-F5 or Shift-F9 and (2) will it execute ALL queries or just the SELECTED queries?

Thanks

[Larry Woods]

More information that is VERY strange.

I created a new SQL dump that includes all of the INSERTS up to, and including the INSERT where SQLYog stops. There are 94 INSERTS. BUT when I execute the SQL dump it stops with an error after loading 60 records, but with the 94th record being displayed as the one in error. So, I remove the last record (INSERT # 94) in the SQL dump and upload it again. No problems!!! I loads 93 records correctly. Something is very confusing. Error after loading 60 records IF the 94th record is included in the file. Remove the 94th INSERT and 93 records get loaded.

'Larry wrote:

I am using 8.82 and am using SQLyogTunnel.php dated 1/12/2011 (46K) that is included in the install of 8.82. I need to upload 10 different SQL dumps into a database. I have been able to upload most of them but when I get to a particular one SQLYog executes the queries for the first 60 records and then stops, with the following error:

Error occured at:2011-03-09 06:26:08

Error Code: 3 – SQLyog tunneling file version mismatch or PHP Error.

Please upload the latest SQLyogTunnel.php from the installation folder of SQLyog.

It also dumps a record in the log. I copy/pasted this record into SQLYog and executed it manually and it loaded just fine!

Also, I have run this sql file twice and it stops at the same record.

HELP!

[Larry Woods]

'Larry wrote:

I have a bunch of tables that have foreign keys but it turns out that alphabetically the tables that reference the foreign keys are before the tables that contain the keys. Therefore I can't restore the database. MySQL doesn't like it if you reference a foreign key if the table that contains the key doesn't exist!

What I want to do is position the referenced tables first in the backup so that I can perform a restore. Otherwise the backup is useless!

Ideas?

Thanks in advance.

I think that I found my answer. SET FOREIGN KEY=0

Right?

[Larry Woods]

'peterlaursen' wrote:

What error.log are you referring to? The MySQL server error log or the SQLyog error log?

The SQLyog error log is in the 'AppData' folder – like any not read-only fies used by SQLyog. It is named “SQLyog.err”.

If you are referring to the MySQL server error log a client generally cannot read it as a client conneccts to the MySQL server and does not access the file system of the server machine. MySQL documentation on the MySQL server error log is here: http://dev.mysql.com/doc/refman/5.1/en/error-log.html. To read the server error log you must open it with a text editor.

Thanks for the quick response. After writing this request I was able to find the SQLYog error log (the one that I was looking for) but I really don't understand why there isn't a link in the SQLYog app so that I don't have to go digging for it.

Just a thought.

[Larry Woods]

'Larry wrote:

This is driving me nuts! I got an error while I was restoring some table via and SQL file. Now, afterward I want to reread the error log but I can't find any link/icon that allows me to do that. I am sure that it's SOMEWHERE but I can't find it.

Suggestion to developers: Put link under HELP

Thanks for any feedback.

[Larry Woods]

I got it to work. I went to “Power Tools”, then “Refresh Tags”.

[Larry Woods]

That was the problem. All is working now. Thanks.

BUT, while I have your attention, can you explain—or point me to an explanation—of the popup that is displayed each time I close SQLyog: “The contents of this tab have been changed. Do you want to save the changes?”

What “tab”?

Thanks

[Larry Woods]

peterlaursen wrote on Jul 27 2008, 02:21 PM:

BTW: If you are coding for Windows/.NET then MySQL has a plugin for Visual Studio.

I never tried it myself, but saw problems reported with other VS versions than 2005.

Documentation: http://dev.mysql.com/doc/refman/5.0/en/con…or-vstudio.html

Go to the bottom of this FTP-page for download: ftp://mirrors.dotsrc.org/mysql/Downloads/Connector-Net/

Thanks, again, but I am not THAT desperate!!! I got the ODBC driver to work so I can use Access VBA.

[Larry Woods]

peterlaursen wrote on Jul 27 2008, 11:01 AM:

anyway .. please tell how you copied.

if SQLyog 'copy to other' leaves cardinality wrongly NULL we should possibly invoke ANALYSE TABLE after copy!

You got it, Peter….

I did a “COPY TO ANOTHER SERVER…”. After seeing your note I did an ANALYZE TABLE and it did fill in the cardinality.

Thanks

[Larry Woods]

Thanks, again, Peter.

I do realize your goal with SQLyog but there doesn't seem to be any good database manipulating tools in the non-Windows world. I agree with the other poster that stored procedures are available but often they leave something to be desired.

Anyway….Thanks

[Larry Woods]

Thnks much, Peter.

It works…naturally!

[Author]

Posts