CODEWITHSUNDEEP
mysqlsql
felix Antony
felix Antony

Reputation: 1460

database copy with same structure having primary key issues

I have a scenario,

I have two databases DB1 and DB2. Both database have same structure.

DB1 contains a table "table1" with AUTO INCREMENT PRIMARY KEY 1,2,3,4,5 and DB2 also contains "table1" with primary key 1,2,3,4,5. when I insert data from DB2 to DB1, the data from DB2 will become 6,7,8,9,10. But I need 1,2,3,4,5 for data from DB2 and 6,7,8,9,10 as DB1 data..

Can I use this query?

mysqldump -n -t -u userB --password=*** db2 | mysql -u userA --password=*** db1

is it possible?

Upvotes: 2

Views: 111

Answers (3)

Jidheesh Rajan
Jidheesh Rajan

Reputation: 4845

DECLARE @MaxId INT
SELECT @MaxId = MAX(Id) FROM Db1

SET IDENTITY_INSERT db1 ON
INSERT INTO Db1(Id, Column1, Column2, ...)
SELECT @MaxId+ ROW_NUMBER()OVER(ORDER BY Id),
    Column1,
    Column2,
    ...
FROM db2
SET IDENTITY_INSERT db1 OFF

Try this..

Upvotes: 1

khanz
khanz

Reputation: 203

You may use one of these two scenorio

First:

  • Copy db1.table1 data in temp table
  • Truncate db1.table1
  • Copy data from db2.table to db1.table1
  • Copy data from temp table to db1.table

Second:

  • Copy db1.table1 data to db2.table1
  • Truncate db1.table1
  • Copy data from db2.table to db1.table1

Use can use this mysql command for copying data:

insert into dbDestination.tblDestination (field1, field2, ...) select ... from dbSource.tblSource

Upvotes: 1

Rakesh Patel
Rakesh Patel

Reputation: 70

First Truncate the "table1" in DB1 before inserting data from DB2 to DB1 because It may have set "Auto increment id=6"

Upvotes: -1

Related Questions