Rectangle 27 181

INSERT INTO table2 (st_id,uid,changed,status,assign_status)
SELECT st_id,from_uid,now(),'Pending','Assigned'
FROM table1

If you want to include all rows from table1. Otherwise you can add a WHERE statement to the end if you want to add only a subset of table1.

Great, glad to see this is the same as what I've been used to in T-SQL.

If You want to copy all table1 data then you must create new table2 before copy @SANDEEP

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 43

If you don't want to list the fields, and the structure of the tables is the same, you can do:

INSERT INTO `table2` SELECT * FROM `table1`;

or if you want to create a new table with the same structure:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

why [AS] is in square brackets, what is done by AS in here

It denotes that the word is optional. It is written that way because I copied and pasted from the docs; it does not add anything. I think the AS is mandatory in other SQL dialects.

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 17

You can easily get data from another table. You have to add fields only you want.

The mysql query is:

INSERT INTO table_name1(fields you want)
  SELECT fields you want FROM table_name2

where, the values are copied from table2 to table1

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 143

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

EDIT: or if the tables have different structures you can also:

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1

What will be happen if someone wants to perform insert operations in source table during running this query ? it locks insert operation or not ?

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 142

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

EDIT: or if the tables have different structures you can also:

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1

What will be happen if someone wants to perform insert operations in source table during running this query ? it locks insert operation or not ?

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 89

If the table doesn't exist, you can create one with the same schema like so:

CREATE TABLE table2 LIKE table1;
INSERT INTO table2 SELECT * FROM table1
SELECT * INTO newTable FROM sourceTable
MySQL

@KasunSiyambalapitiya SELECT ... INTO is meant to export a table to an output file, or to variables; not directly into a table. See dev.mysql.com/doc/refman/5.7/en/select-into.html

@Kasun Siyambalapitiya that w3school page is for a different SQL, not intended for MySQL. w3schools now has error reporting, if you find issues report it on their site to assist with accurate knowledge.

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 89

If the table doesn't exist, you can create one with the same schema like so:

CREATE TABLE table2 LIKE table1;
INSERT INTO table2 SELECT * FROM table1
SELECT * INTO newTable FROM sourceTable
MySQL

@KasunSiyambalapitiya SELECT ... INTO is meant to export a table to an output file, or to variables; not directly into a table. See dev.mysql.com/doc/refman/5.7/en/select-into.html

@Kasun Siyambalapitiya that w3school page is for a different SQL, not intended for MySQL. w3schools now has error reporting, if you find issues report it on their site to assist with accurate knowledge.

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 3

You can setup federated tables, which is basically linking a table on one server to a table on another. Then use the federation to do your data transfers.

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

Then you can query it like any other table.

There are however a decent number of limitations you should read about including the remote password being stored in plain text. If this was a temporary setup purely for a once off copy, and the server isn't available to the public you have already minimised most of the risk associated with it though.

Copy a table with data from one MySQL server to another - Stack Overfl...

mysql sql-server database copy
Rectangle 27 23

If table1 is large and you don't want to lock it for the duration of the copy process, you can do a dump-and-load instead:

CREATE TABLE table2 LIKE table1;

SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;
LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table2;

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 23

If table1 is large and you don't want to lock it for the duration of the copy process, you can do a dump-and-load instead:

CREATE TABLE table2 LIKE table1;

SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;
LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table2;

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 3

Intuitive :) I like it. It is a normal SELECT statement, preceded with one line of "INSERT INTO".

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 4

INSERT INTO seatreservation (seat_ID, reservation_ID) 
SELECT ID, 1 FROM seat WHERE venue_ID = 2;

You Mr Shibormot are a life saver ! Thank you.

So for example if i want to add an additional field and enter :Available as value can i do something like this ? INSERT INTO seatreservation (seat_ID, reservation_ID,Flag) SELECT ID, 1 'Available' FROM seat WHERE venue_ID = 2; But its not working. Please explain your query to me and how the parser can interpret that 1 value is for reservation but cant interpret Available string for Flag.

@FrankSMallville, what type is your Flag column of? And what is error message?

FLag is of type String and the error message is just a wrong sql statement.

sql - mysql copy from one table column to another empty table with int...

mysql sql sql-server database relational-database
Rectangle 27 9

+1 - although the new table won't have the index definitions from the first. The "create ... like ..." approach will copy index definitions too.

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 9

+1 - although the new table won't have the index definitions from the first. The "create ... like ..." approach will copy index definitions too.

sql - In MySQL, how to copy the content of one table to another table ...

sql mysql database
Rectangle 27 2

CREATE TABLE newTable LIKE oldTable;
INSERT INTO newTable SELECT * FROM oldTable;

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 1

INSERT INTO Table1(Column1,Column2..) SELECT Column1,Column2.. FROM Table2 [WHERE <condition>]

While this code may answer the question, it would be better to explain how it solves the problem and why to use it. Code-only answers are not useful in the long run.

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 1

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Doesn't work. Per the docs, "MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension"

How to copy data from one table to another new table in MySQL? - Stack...

mysql copy
Rectangle 27 8

UPDATE table SET X=Y, Y=X will do precisely what you want (edit: in PostgreSQL, not MySQL, see below). The values are taken from the old row and assigned to a new copy of the same row, then the old row is replaced. You do not have to resort to using a temporary table, a temporary column, or other swap tricks.

@D4V360: I see. That is shocking and unexpected. I use PostgreSQL and my answer works correctly there (I tried it). See the PostgreSQL UPDATE docs (under Parameters, expression), where it mentions that expressions on the right hand side of SET clauses explicitly use the old values of columns. I see that the corresponding MySQL UPDATE docs contain the statement "Single-table UPDATE assignments are generally evaluated from left to right" which implies the behaviour you describe.

Thanks Greg and D4V360, good to know the differences in PostgreSQL and MySQL about the behavior of the update queries.

The "x=y, y=x" approach also works in Oracle, for what it's worth.

IMHO this answer is a mess - bad advice with "oops never mind" appended. Half of it should be a comment and the only part of the remainder that's relevant to the question is the link to MySQL docs...

database - Swapping column values in MySQL - Stack Overflow

mysql database
Rectangle 27 2

Because on this table Therefore.dbo.TheCase6 for CaseNo you have specify Not NULL Constraints something like this

CaseNo int NOT NULL

But you did not select the CaseNo column from the dbo.thecat62 table, so you are explicitly trying to insert nulls into a non-nullable column.

You just need to select the CaseNo column, as well, presuming it does not contain any nulls in teh source table.

INSERT INTO dbo.thecase6 (Job_Start_Date,CaseNo) 
SELECT Work_Order_Job_Start_Date,CaseNo FROM dbo.thecat62

Thank you for your reply. I have added the caseno together with another column called Job_Number as without the job_number, it will also throw out the error Cannot insert the value NULL into column 'Job_Number', table 'Therefore.dbo.TheCase6'; column does not allow nulls. INSERT fails. Hence I have added the job_number but then it shows an error Violation of PRIMARY KEY constraint 'TheCase6PK'. Cannot insert duplicate key in object 'dbo.TheCase6'. Since this is the issue, will anupdate instead of an insert work? How would I do the update query?

mysql - how to copy data from one SQL column table to another SQL colu...

mysql sql-server database sql-server-2008 tsql
Rectangle 27 2

Because MySql cannot run sub selects in update statements if the subselect is the same table as the one in the update, you need to first temporarily copy the data into another table - so DO NOT run this when data is still going into the database as it is not an atomic operation. You need to shut down any applications using the database first.

-- Create a copy of the data 
CREATE TABLE table_name_copy SELECT * FROM table_name;

-- Update the rows with the most Icecream_consumed to have the total 
UPDATE table_name t1 SET t1.Icecream_consumed = (
    SELECT SUM(t2.Icecream_consumed) 
    FROM table_name_copy t2 
    WHERE t2.Firstname = t1.FirstName AND t2.Lastname = t1.Lastname
)
WHERE id = (
    SELECT id 
    FROM table_name_copy t2 
    WHERE t2.Firstname = t1.FirstName AND t2.Lastname = t1.Lastname
    ORDER BY Icecream_consumed DESC LIMIT 1
);

-- Delete the other rows that don't have the new totalled Icecream_consumed 
DELETE FROM table_name
WHERE id != (
    SELECT t2.id 
    FROM table_name_copy t2 
    WHERE t2.Firstname = table_name.FirstName AND t2.Lastname = table_name.Lastname
    ORDER BY Icecream_consumed DESC LIMIT 1
);

-- Remove the copied table
DROP TABLE table_name_copy;

This is an option I hadn't considered and it makes sense, nice work. I now wonder if there is a way to do the same operation without creating a temporary table.

The only thing I can think off the top of my head is to write a small script or application to do the data migration. But to be honest that is probably more work. I've fixed some mistakes and added a working example, to this answer, to show it works.

mysql: add integer values with duplicate primary key - Stack Overflow

mysql