If you want to copy a table from one Database to another database , You can simply do as below.   

    CREATE TABLE db2.table LIKE db1.table;
    INSERT INTO db2.table SELECT * FROM db1.table;

or just CREATE TABLE db2.table SELECT * FROM db1.table in MySQL 5


In BASH you can do:

    mysqldump database_1 table | mysql database_2

CREATE TABLE db2.table_new AS SELECT * FROM db1.table_old

If you just want Structure to be copied simply use

`CREATE TABLE Db_Name.table1 LIKE DbName.table2;`

Ps &gt; that will not copy schema and data

simply use - 

  CREATE TABLE DB2.newtablename SELECT * FROM DB1.existingtablename;


In Commandline:

    mysqldump -h localhost -u username -ppassword [SCHEMA] --tables [TABLE] | mysql -h otherhost -u username -ppassword [SCHEMA2]

This will copy table inside SCHEMA on localhost to SCHEMA2 on otherhost.

localhost and otherhost are just hostname and might be same or different.

I have a Django application that reads data from a web API and puts it in a database.  
Is there a way to create a new object from a mode but prevent the duplicate exception if the object already exists?  

In other words, is there a way to save an object, but to just do nothing if it already exists?

Create if doesn&#39;t exist

When I do a Git diff, it shows the code with the diff and it looks great. But how do I go to the next page or the next document.

How do I navigate in the results of Diff

I want to copy a table&#39;s schema as well as the data within that table to another database table in another database on a live server.  How could I do this?


I want to copy table contained from one database and insert onto another database table

<p>I want to copy a table's schema as well as the data within that table to another database table in another database on a live server.  How could I do this?</p>


I am using MySQL. I have a table called EMP, and now I need create one more table (EMP_TWO) with same schema, same columns, and same constraints. How can I do this?

Create table in MySQL that matches another table?

How do I write an IF ELSE statement in a MySQL query? 

Something like this:

    mysql_query(&quot;...(irrelevant code).. IF(action==2&amp;&amp;state==0){state=1}&quot;);

Then down in my array I should be able to do this:
              
     $row[&#39;state&#39;] 
    //this should equal 1, the query should not change anything in the database, 
    //just the variable for returning the information

How do write IF ELSE statement in a MySQL query

    try {
    	$db = new PDO(&quot;mysql:host=&quot;.HOST.&quot;;dbname=&quot;.DB, USER, PW);
    	$st = $db-&gt;prepare(&quot;SELECT * FROM c6ode&quot;);
    }
    catch (PDOException $e){
    	echo $e-&gt;getMessage();
    }

How can I check the mysql error for the query in above case? 

How to view query error in PDO PHP

The title is my question. I googled and try something like

    mysql&gt; !\ clear
    mysql&gt; !\ cls
    mysql&gt; system cls
    mysql&gt; system clear 
    
    blah blah ...

but none of them works.

Anyone show me how to clear screen, just like `cls` command in Windows?


How to clear MySQL screen console in Windows?

I have a case where getting the table name should be from a set variable like: 

    SET @ID_1 = (SELECT ID FROM `slider` LIMIT 0,1);
    SET @Cat = (SELECT Category FROM `slider` LIMIT 0,1);
    select * from @Cat where ID = @ID_1
but doing that way MySQL outputs an error, so could someone show me how I can achieve that, because these are my baby steps in MySQL.

How to select from MySQL where Table name is Variable

I have a largish table in an Excel worksheet:

    Column_1 | Column_2 | Column_3

    ValueA       ValueB     ValueC
    ....

What I need is a function that will take as input the range and an SQL-like query String and return a range of rows that match the query, e.g.:

    =SQL_SELECT(A1:C1000, &quot;SELECT * WHERE Column_1 = ValueH AND Column_3 = blah&quot;)


Does something like this exist? Or what would be the best way to implement myself?

Excel function to make SQL-like queries on worksheet data?

When I have a column with separated values, I can use the `unnest()` function:

    myTable
    id | elements
    ---+------------
    1  |ab,cd,efg,hi
    2  |jk,lm,no,pq
    3  |rstuv,wxyz
    
    select id, unnest(string_to_array(elements, &#39;,&#39;)) AS elem
    from myTable
    
    id | elem
    ---+-----
    1  | ab
    1  | cd
    1  | efg
    1  | hi
    2  | jk
    ...

How can I include element numbers? I.e.:

    id | elem | nr
    ---+------+---
    1  | ab   | 1
    1  | cd   | 2
    1  | efg  | 3
    1  | hi   | 4
    2  | jk   | 1
    ...

I want the *original position* of each element in the source string. I&#39;ve tried with window functions (`row_number()`, `rank()` etc.) but I always get `1`. Maybe because they are in the same row of the source table?

I know it&#39;s a bad table design. It&#39;s not mine, I&#39;m just trying to fix it.

PostgreSQL unnest() with element number

I have a table that is missing a column in its primary key constraint.  Instead of editing it through SQL Server, I want to put this in a script to add it as part of our update scripts.

What syntax can I use to do this? Must I drop and recreate the key constraint?

How can I alter a primary key constraint using SQL syntax?

I am working with a query which contains &quot;CASE&quot; statement within &quot;WHERE&quot; clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:

    SELECT
        tl.storenum &#39;Store #&#39;, 
        co.ccnum &#39;FuelFirst Card #&#39;, 
        co.dtentered &#39;Date Entered&#39;,
        CASE st.reasonid 
            WHEN 1 THEN &#39;Active&#39; 
	   WHEN 2 THEN &#39;Not Active&#39; 
	   WHEN 0 THEN st.ccstatustypename 
	   ELSE &#39;Unknown&#39; 
        END &#39;Status&#39;,
        CASE st.ccstatustypename 
            WHEN &#39;Active&#39; THEN &#39; &#39; 
	   WHEN &#39;Not Active&#39; THEN &#39; &#39; 
	   ELSE st.ccstatustypename 
        END &#39;Reason&#39;,
        UPPER(REPLACE(REPLACE(co.personentered,&#39;RT\\\\&#39;,&#39;&#39;),&#39;RACETRAC\\\\&#39;,&#39;&#39;)) &#39;Person Entered&#39;,
        co.comments &#39;Comments or Notes&#39;
    FROM 
        comments co
        INNER JOIN cards cc ON co.ccnum=cc.ccnum
        INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid
        INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid
        INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd
        INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid
        LEFT JOIN stores s ON s.StoreNum = tl.StoreNum
    WHERE 
        CASE LEN(&#39;TestPerson&#39;)
            WHEN 0 THEN co.personentered  = co.personentered
	   ELSE co.personentered LIKE &#39;%TestPerson&#39;
        END 
        AND cc.ccnum = CASE LEN(&#39;TestFFNum&#39;)
            WHEN 0 THEN cc.ccnum 
	   ELSE &#39;TestFFNum&#39; 
        END 
        AND CASE LEN(&#39;2011-01-09 11:56:29.327&#39;) 
            WHEN 0 THEN co.DTEntered = co.DTEntered 
	   ELSE 
	       CASE LEN(&#39;2012-01-09 11:56:29.327&#39;) 
	           WHEN 0 THEN co.DTEntered &gt;= &#39;2011-01-09 11:56:29.327&#39; 
		  ELSE co.DTEntered BETWEEN &#39;2011-01-09 11:56:29.327&#39; AND &#39;2012-01-09 11:56:29.327&#39; 
	       END 
        END
        AND tl.storenum &lt; 699 
    ORDER BY tl.StoreNum


&quot;CASE&quot; statement within &quot;WHERE&quot; clause in SQL Server 2008

I&#39;m trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I&#39;ve dropped and re-created tables before but I&#39;ve never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: [ALTER TABLE (Transfact-SQL)][1] however I can&#39;t make heads or tails of it.

I have the following so far (essentially nothing unfortunately):

    alter table [progennet_dev].PROGEN.LE
  	alter column UR_VALUE_3

How do I approach this? Is there better documentation for this statement out there (I did some searches for an example statement but came up empty)?


  [1]: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Changing the maximum length of a varchar column?

My application download mails over IMAP and stores them in a MySQL database. Earlier I was supporting mails size upto 10 MB and hence a &#39;mediumtext&#39; column to store the mail content was enough. Now I need to support mails upto 30MB. So I changed the datatype for the column to &#39;largetext&#39;. Yesterday a mail with size 25 MB was stored. After that whenever I execute mysqldump command it throws error:
&lt;pre&gt;
mysqldump: Error 2020: Got packet bigger than &#39;max_allowed_packet&#39; bytes when dumping table `ib_mailbox_backup` at row: 3369
&lt;/pre&gt;

Row 3369 contains the 25 MB mail.

In MySQL config I increased the &#39;max_allowed_packet&#39; from 64M to 512M and it still fails with the same error. Executing the mysqldump command on the same machine where MySQL server is running. How do I solve this? 

mysqldump error: Got packet bigger than max_allowed_packet&#39;

with the following statement:
    
    mysqldump --complete-insert --lock-all-tables --no-create-db 
    --no-create-info --extended-insert --password=XXX -u XXX 
    --dump-date yyy &gt; yyy_dataOnly.sql
I get INSERT statements like the following:

    INSERT INTO `table` VALUES (1,&#39;something&#39;),(2,&#39;anything&#39;),(3,&#39;everything&#39;);

What I need in my case is something like this:

    INSERT INTO `table` VALUES (1,&#39;something&#39;);
    INSERT INTO `table` VALUES (2,&#39;anything&#39;);
    INSERT INTO `table` VALUES (3,&#39;everything&#39;);


Is there a way to tell &quot;mysqldump&quot; to create a new INSERT statement for each row?
Thanks for your help!

MySQLDump one INSERT statement for each data row

I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. `mysqldump --no-data` would almost do the job, but it keeps the auto_increment values. Is there any way to do it without using PHPMyAdmin (that I know it can do it)?

mysqldump - Export structure only without autoincrement

This has been asked a few times but I cannot find a resolution to my problem. Basically when using mysqldump, which is the built in tool for the MySQL Workbench administration tool, when I dump a database using extended inserts, I get massive long lines of data. I understand why it does this, as it speeds inserts by inserting the data as one command (especially on InnoDB), but the formatting makes it REALLY difficult to actually look at the data in a dump file, or compare two files with a diff tool if you are storing them in version control etc. In my case I am storing them in version control as we use the dump files to keep track of our integration test database.

Now I know I can turn off extended inserts, so I will get one insert per line, which works, but any time you do a restore with the dump file it will be slower.

My core problem is that in the OLD tool we used to use (MySQL Administrator) when I dump a file, it does basically the same thing but it FORMATS that INSERT statement to put one insert per line, while still doing bulk inserts. So instead of this:

    INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES (887,&#39;0.0000&#39;),191607,&#39;1.0300&#39;);

you get this:

    INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES 
     (887,&#39;0.0000&#39;),
     (191607,&#39;1.0300&#39;);

No matter what options I try, there does not seem to be any way of being able to get a dump like this, which is really the best of both worlds. Yes, it take a little more space, but in situations where you need a human to read the files, it makes it MUCH more useful.

Am I missing something and there is a way to do this with MySQLDump, or have we all gone backwards and this feature in the old (now deprecated) MySQL Administrator tool is no longer available?

Using mysqldump to format one insert per line?

I&#39;m in the process of moving my files onto another computer, and one thing I would like to do is to transfer the data in my MySQL database. I want to dump the databases into .sql files, but also have the create database db_name including in the file, that way I just have to import the file to mysql without having to manually create the databases. Is there a way to do that?

Content Type	Original Author	Original Content on Stackoverflow
Question	user1031092	View Question on Stackoverflow
Solution 1 - Mysql	user319198	View Answer on Stackoverflow
Solution 2 - Mysql	HukeLau_DABA	View Answer on Stackoverflow
Solution 3 - Mysql	Eaten by a Grue	View Answer on Stackoverflow
Solution 4 - Mysql	Rashi Goyal	View Answer on Stackoverflow
Solution 5 - Mysql	Hemant Shori	View Answer on Stackoverflow
Solution 6 - Mysql	Vishnu More	View Answer on Stackoverflow
Solution 7 - Mysql	DrGeneral	View Answer on Stackoverflow

I want to copy table contained from one database and insert onto another database table

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

Solution 6 - Mysql

Solution 7 - Mysql

How do I navigate in the results of Diff

Create if doesn't exist

Attributions