CODEWITHSUNDEEP
mysqlsql
KoZe
KoZe

Reputation: 63

How to copy rows from one MySQL database to another

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?

Upvotes: 4

Views: 10551

Answers (6)

Palec
Palec

Reputation: 13551

Disclaimer: Originally the OP tagged the question as both and . 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.

Upvotes: 0

Atul Singh Rajpoot
Atul Singh Rajpoot

Reputation: 439

You can do like the following:

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

Upvotes: 10

SaravanaC
SaravanaC

Reputation: 416

Try the below code:

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

Upvotes: 0

Suraj Singh
Suraj Singh

Reputation: 4059

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

Check Here

Upvotes: 0

Ajay
Ajay

Reputation: 6590

Try this query 

INSERT Table2
SELECT * FROM Table1
WHERE [Conditions]

Upvotes: 0

Giorgio Minardi
Giorgio Minardi

Reputation: 2775

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

Upvotes: 0

Related Questions