1

I have two tables as it is seen in the schematic picture. I want to copy data from three columns URI, fields and details in table1 and insert them into table2 if date in table1 is greater than 12/11/2013. Something like below query:

INSERT INTO table2 (all_links, fields_one, fields_two) FROM table1 (URI, fields, details) WHERE date>"12-11-2013 00-00-00";

Could you please help to solve this problem?

schematic picture

Sami
  • 1,473
  • 1
  • 17
  • 37
  • possible duplicate of [Select multiple columns from a table and insert data into another table in a different database in PHP-MySQL](http://stackoverflow.com/questions/22594005/select-multiple-columns-from-a-table-and-insert-data-into-another-table-in-a-dif) – halfer Mar 28 '14 at 10:26
  • Hi there. Just so you know, we tend to recommend that questions are not duplicated here, as we feel they create duplicate work. There's a couple of your questions that appear to be like this. Since you're new, you need not worry about it, but if you can compress each problem into a single question, rather than asking another one to do the same thing, that does help. – halfer Mar 28 '14 at 10:28

3 Answers3

2

Do it like

INSERT INTO table2 (all_links, fields_one, fields_two) 
select URI, fields, details FROM table1 
WHERE date > "12-11-2013 00-00-00";

In case tables are in different DB (assuming dbo is default owner)

INSERT INTO DB1.dbo.table2 (all_links, fields_one, fields_two) 
select URI, fields, details FROM DB2.dbo.table1 
WHERE date > "12-11-2013 00-00-00";
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • thanks for your response. I tried your code and got this error: `Parse error: syntax error, unexpected T_STRING in...`. Is there something missed in syntax? – Sami Mar 21 '14 at 21:30
  • Try now, with the edited query (using the entire datetime string) – Rahul Mar 21 '14 at 21:35
  • could you please modify your code if tables are in different databases? thanks, – Sami Mar 23 '14 at 15:30
  • Just use `dbname.owner.tablename` instead of `tablename`. See edit. – Rahul Mar 23 '14 at 21:27
1

Try something like this: 

   INSERT INTO tbl_temp2 (fld_id)
      SELECT tbl_temp1.fld_order_id
      FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

And check the documentation:

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

user3441996
  • 99
  • 9
1
INSERT INTO table2 (all_links, fields_one, fields_two) select URI, fields, details from table1 where date>"12-11-2013 00-00-00";
akr
  • 739
  • 4
  • 15