How to copy rows from one MySQL database to another

Question

I have two tables in different databases (WAMP server) with the same structure. I want to copy from the first database (newkt) to the second one (oldkt) all rows that do not exist in the second database (oldkt).

newkt   -> table : users (1500 records) (id, name, password)
oldkt   -> table : users (1200 records) (id, name, password)

I want to actually add rows to the oldkt database whose id doesn’t exist in oldkt yet.

Also if I have more than 3 columns, can these be added automatically or I do have to tag all of them?

use normal SQL but prefix table names with database name + dot like here http://stackoverflow.com/questions/2132654/querying-multiple-databases-at-once — rkosegi, Jan 06 '14 at 11:47
What you have tried upto now? if you show your sql statement than it will be easier to guide you. — I kiet, Jan 06 '14 at 11:47
Nothing up untill now I am afraid that I will mess up the tables..!!! — KoZe, Jan 06 '14 at 11:59
Before you do ANYTHING with ANYTHING . EVER . make a back up. — Strawberry, Jan 06 '14 at 12:37

score 10 · Accepted Answer · answered Jan 06 '14 at 13:34

10

You can do like the following:

    insert into database1.table  select * from database2.table where id not in(select id from database1.table);

answered Jan 06 '14 at 13:34

Atul Singh Rajpoot

439
2
9

score 0 · Answer 2 · answered Jan 06 '14 at 11:52

0

As said you should be able to perform a typical insert but by specifying the database name in the query:

SELECT * 
INTO TargetDatabase.dbo.TargetTable
FROM SourceDatabase.dbo.SourceTable

answered Jan 06 '14 at 11:52

Giorgio Minardi

2,765
1
15
11

Thanks a lot for the quick response – KoZe Jan 06 '14 at 11:58
I get an error Undeclared variable: oldkt_ which is the name of the first database – KoZe Jan 06 '14 at 11:58

score 0 · Answer 3 · edited Jan 06 '14 at 12:56

0

Try the below code:

INSERT INTO TargetDatabase.dbo.TargetTable
SELECT * FROM SourceDatabase.dbo.SourceTable 
EXCEPT 
SELECT * FROM TargetDatabase.dbo.TargetTable

edited Jan 06 '14 at 12:56

Bridge

29,818
9
60
82

answered Jan 06 '14 at 12:27

SaravanaC

396
2
4

So I tried the following INSERT INTO oldkt_.dbo.jos_users SELECT * FROM kenaktoolsdata.dbo.jos_users EXCEPT SELECT * FROM oldkt_.dbo.jos_users But I get the error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.jos_users SELECT * FROM kenaktoolsdata.dbo.jos_users EXCEPT SELECT * FROM oldkt' at line 1 – KoZe Jan 06 '14 at 12:36
above code was given for SQL Server, in this case for MY SQL please use the below code with left outer join 'INSERT TargetDatabase.dbo.TargetTable SELECT a.* FROM SourceDatabase.dbo.SourceTable a LEFT JOIN TargetDatabase.dbo.TargetTable b ON a.col1=b.col1 and ..... WHERE b.col1 IS NULL or b.col2 is null ..... ' – SaravanaC Jan 06 '14 at 13:07

Suraj Singh · Answer 4 · 2014-01-06T12:44:33.863

0

INSERT INTO oldkt   
SELECT id , name ,password FROM newkt WHERE id NOT IN (SELECT id FROM oldkt )

Check Here

edited Jan 06 '14 at 12:44

answered Jan 06 '14 at 12:39

Suraj Singh

4,041
1
21
36

score 0 · Answer 5 · answered Jan 06 '14 at 12:43

0

Try this query

INSERT Table2
SELECT * FROM Table1
WHERE [Conditions]

answered Jan 06 '14 at 12:43

Ajay

6,418
18
79
130

score 0 · Answer 6 · edited May 23 '17 at 12:33

0

Disclaimer: Originally the OP tagged the question as both mysql and sql-server. I provided answer for both, leaving it at that as it is a more general solution than a purely MySQL one. When using MySQL, be sure to replace .. in the query with ..

SELECT … INTO table does not work in MySQL (although it works in SQL Server) so INSERT … SELECT will help you. It works in both MySQL and SQL Server.

You can specify table name including database name using . (dot) delimiter in MySQL and .. (double dot) delimiter in SQL Server. (See also a related question.)

EXCEPT does not work in MySQL so instead we will use LEFT JOIN and WHERE. (See also a related question.)

For SQL Server:

INSERT INTO oldkt..users
SELECT newkt..users.*
FROM
    newkt..users
    LEFT JOIN oldkt..users USING(id)
WHERE
    oldkt..users.id IS NULL

When used in MySQL, just replace the .. with ..

My solution works under the assumption that id is unique key in both tables. (Primary key is always unique.) If this is not satisfied, the join condition must be adjusted to that fact.

edited May 23 '17 at 12:33

Community

1
1

answered Jan 06 '14 at 14:18

Palec

12,743
8
69
138

Thanks a lot for the answer but I get this error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.jos_users SELECT kenaktoolsdata..jos_users.* FROM kenaktoolsdata..jos_us' at line 1 INSERT INTO oldkt_..jos_users SELECT kenaktoolsdata..jos_users.* FROM kenaktoolsdata..jos_users LEFT JOIN oldkt_..jos_users USING(id) WHERE oldkt_..users.id IS NULL – KoZe Jan 06 '14 at 15:08
@KoZe You used the query I wrote for MS SQL Server in MySQL. As I wrote, replace all the `..` with `.` and it will work for you. Also see the comments below your question, especially the one asking you to specify if you use MySQL or SQL Server. I provided answer for both. – Palec Jan 06 '14 at 15:15
I did and had the same error ... anyway this worked insert into database1.table select * from database2.table where id not in(select id from database1.table); might the ; did the trick ?? Its mySql – KoZe Jan 06 '14 at 15:36
@Koze If you replaced all the `..` with `.` you could not get the same error because the original error message contains `..`. If you are running a query through `mysql` command, you always need to add `;` at the end as otherwise it does not get executed. I edited your question to reflect that you are asking a MySQL question. – Palec Jan 06 '14 at 15:47

How to copy rows from one MySQL database to another

6 Answers6