Insert an specific row from a table in a database A to another table in a database B

Question

I want to copy a row for example whith ID=23 in a table A into another table with ID=30 into another database. I got this

INSERT INTO pacaya_control_venta.tb_venta_pacaya WHERE ID=33
SELECT * FROM llamadas.tb_venta_pacaya WHERE ID = 48;

But I think the double WHERE is wrong. Any thoughts???

Please [edit] your post and add some example data and the database schema. Also, please add the error message you get. Also, please tell us why you talk about `ID=23` and `ID=30` but your SQL shows `ID=33` and `ID=48`. — Corion, Jan 14 '19 at 19:53
Your inital SQL (before your edit) was syntatically wrong. The "double where" is also incorrect. See this duplicate for the correct syntax: [mysql :: insert into table, data from another table?](https://stackoverflow.com/questions/4241621/mysql-insert-into-table-data-from-another-table). If you need to alter the contents of the row you're inserting, use a [values](https://www.w3schools.com/sql/sql_insert.asp) clause. — paulsm4, Jan 14 '19 at 19:53
@paulsm4 Maybe it's not a duplicate, because I think they want to change the value of the `ID` column, but they have to [edit] their question to answer that. — Corion, Jan 14 '19 at 19:54

score 0 · Answer 1 · answered Jan 14 '19 at 19:55

0

Is this what you need?

INSERT INTO pacaya_control_venta.tb_venta_pacaya (id, col1,col2,col3,col4,...)
  SELECT 33, col2, col3, col4,.... FROM llamadas.tb_venta_pacaya WHERE ID = 48;

answered Jan 14 '19 at 19:55

Derviş Kayımbaşıoğlu

28,492
4
50
72

score 0 · Accepted Answer · answered Jan 14 '19 at 19:59

Assuming both the table contain the same columns (col1, col2, ...) and the db are on the same server for copy a row you should use update .. not insert

UPDATE pacaya_control_venta.tb_venta_pacaya  a
INNER JOIN llamadas.tb_venta_pacaya  b ON a.id=33 and b.id = 48 
SET a.col1 = b.col1,
    a.col2 = b.col2, 
    .....

insert is for create a new row not for updated existing one

INSERT INTO pacaya_control_venta.tb_venta_pacaya 
SELECT * 
FROM llamadas.tb_venta_pacaya 
WHERE ID = 48;

Insert an specific row from a table in a database A to another table in a database B

2 Answers2