1

Hi and thank you for reading.

I have two tables with the same columns. I would like to first table's data to second table. Insert query is very simple: INSERT INTO Table1 SELECT * FROM Table2. But my problem is when I upgrade OpenERP, table column positions are changed. So INSERT INTO Table1 SELECT * FROM Table2 query doesn't work. Can you help me to solve this???? Any answers will be highly appreciated...thank you!!!

Table1:           Table2:
NAME   COUNT      NAME   COUNT
name1  1          name3  3
name2  2          name4  4
name3  3          name5  5
name4  4          name6  6

SOLUTION: Use information_schema.columns table.

SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='tablename'
Zeck
  • 6,433
  • 21
  • 71
  • 111

1 Answers1

3

Try this query:

INSERT INTO Table1 (name1, name2, name3, name4) 
    SELECT name3, name4, name5, name6
    FROM Table2;
Dan
  • 885
  • 6
  • 11
  • Thank you for reply. But I can't use it. Because I'm write this query to so many tables. I need very flexible query like 'INSERT INTO Table1 SELECT * FROM Table2' – Zeck May 02 '11 at 01:40
  • I recommend doing a "DESCRIBE tableName" SQL query to get the fields of a table then dynamically creating the query above. – Dan May 02 '11 at 01:48
  • 1
    Sorry. I assumed you were using MySQL (DESCRIBE TABLE tableName). Here's the correct solution: http://stackoverflow.com/questions/109325/postgresql-describe-table – Dan May 02 '11 at 02:10
  • 1
    You probably want Vinko Vrsalovic's answer from that link, the `\d tablename` command is for the `psql` command line tool but using `information_schema` will work from any old connection to the database. – mu is too short May 02 '11 at 02:14