-1

Want to import users from a table(re_user) to another table(pg_users). Can you help me? Thnx.

INSERT INTO `pg_users`(`id`, `fname`, `sname`, `status`, `confirm`, `login`, 
`password`, `lang_id`, `email`, `date_birthday`, `date_last_seen`, 
`date_registration`, `root_user`, `guest_user`, `login_count`, `active`, 
`show_info`, `phone`, `user_type`, `access`, `version_message_count`, `rating`,
`about_me`, `social_data`, `date_modified`)

SELECT `id`, `fname`, `sname`, `status`, `confirm`, `login`, `password`, `lang_id`,
`email`, `date_birthday`, `date_last_seen`, `date_registration`, `root_user`,
`guest_user`, `login_count`, `active`, `show_info`, `phone`, `user_type`,
`access`,`version_message_count`, `rating`, `about_me`, `social_data`, 
`date_modified` FROM `re_user` WHERE 1
vonUbisch
  • 1,384
  • 17
  • 32
user3092710
  • 1
  • 1
  • 3
    Please be more descriptive by providing any errors you are experiencing and/or the results you are currently getting or simply, why isn't this working for you. – Anil Dec 11 '13 at 20:15
  • Please check the documentation first: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html – MillaresRoo Dec 11 '13 at 20:17
  • You have an issue with your where clause of select query. – Parkash Kumar Dec 11 '13 at 20:18
  • possible duplicate of [how to import a single table in to mysql database using command line](http://stackoverflow.com/questions/5387619/how-to-import-a-single-table-in-to-mysql-database-using-command-line) – a14m Dec 11 '13 at 20:21
  • INSERT INTO `pg_users`(`id`, `fname`, `sname`, `status`, `confirm`, `login`, `password`, `lang_id`, `email`, `date_birthday`, `date_last_seen`, `date_registration`, `root_user`, `guest_user`, `login_count`, `active`, `show_info`, `phone`, `user_type`, `access`, `version_message_count`, `rating`, `about_me`, `social_data`, `date_modified`) SELECT re_user WHERE re_user > 100; – user3092710 Dec 11 '13 at 20:34
  • You can edit your post to include your updated information. No need to add them as comments. Just an FYI. Makes it easier for people to help you. :) – Anil Dec 11 '13 at 20:35
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE re_user > 100' at line 2 – user3092710 Dec 11 '13 at 20:35
  • The information in the error you received and what you posted don't match up. The where clause in your error is different than the where clause in your post. It will be challenging to get to the bottom of this, if it is not clear as to what code you are really trying to fix. – Anil Dec 11 '13 at 20:36
  • I have 2 DB.Want to transfer users from table re_user of first DB to table pg_users from second DB. – user3092710 Dec 11 '13 at 20:41
  • Ahhhh, that is the description I was looking for! – Anil Dec 11 '13 at 20:42
  • Have an issue.#1142 - SELECT command denied to user 'DB'@'localhost' for table 're_user' – user3092710 Dec 11 '13 at 21:01

2 Answers2

0

Based on what you disclosed in your comments, that you want to insert data into a table by pulling it from a table in another database, you must be specific as to which database this information is coming from, and going to. Update the DESTINATION_SCHEMA_NAME and the SOURCE_SCHEMA_NAME in my example below and that should resolve the issue. Also, if you are copying everything, there is no need for the where clause in your select statement.

INSERT INTO `DESTINATION_SCHEMA_NAME.`pg_users`(`id`, `fname`, `sname`, `status`, `confirm`, `login`, 
`password`, `lang_id`, `email`, `date_birthday`, `date_last_seen`, 
`date_registration`, `root_user`, `guest_user`, `login_count`, `active`, 
`show_info`, `phone`, `user_type`, `access`, `version_message_count`, `rating`,
`about_me`, `social_data`, `date_modified`)

SELECT `id`, `fname`, `sname`, `status`, `confirm`, `login`, `password`, `lang_id`,
`email`, `date_birthday`, `date_last_seen`, `date_registration`, `root_user`,
`guest_user`, `login_count`, `active`, `show_info`, `phone`, `user_type`,
`access`,`version_message_count`, `rating`, `about_me`, `social_data`, 
`date_modified` FROM `SOURCE_SCHEMA_NAME`.`re_user`
Anil
  • 2,539
  • 6
  • 33
  • 42
  • Have an issue.#1142 - SELECT command denied to user 'DB'@'localhost' for table 're_user' – user3092710 Dec 11 '13 at 21:17
  • that means that whatever database user you are using, doesn't have access to that database/table. If you still wish to copy data from one database into another, your user account needs to have access to both databases and to both tables in those databases. – Anil Dec 11 '13 at 21:20
  • Let me tell you more,i have a realestate website and want to transfer users and listings to another website.What is the best method? Thank you in advance. – user3092710 Dec 11 '13 at 21:25
  • If you have the ability to, you can grant access to the database that the user doesn't have access to so that you can run the insert command, and than remove that access after to ensure that user account remains secure. – Anil Dec 11 '13 at 21:25
  • If you want to transfer more than one table, you can try using a program called "MySQL Workbench" to connect to the source database, export the data to an SQL file, and then connect to your destination database, and import those records. – Anil Dec 11 '13 at 21:26
  • I will download now MySQL Workbench. – user3092710 Dec 11 '13 at 21:30
  • Error Number: 1054 Unknown column 'date_created' in 'field list' – user3092710 Dec 12 '13 at 22:12
0

This is how you can do that.

Command Line

Import / Export for single table:

Exporting table schema

 -> mysqldump -u your_user_name -p your_database_name table_name > test.sql

This will create a file named test.sql and creates table sql command to create table table_name.

Importing data into table

 -> mysql -u your_user_name -p database_name table_name < test.sql

Make sure your test.sql file is in the same directory, if not navigate through the path and then run the command.

Prabhakar
  • 6,458
  • 2
  • 40
  • 51