How to write single query to select data from server database insert into local database

Question

0.00/5 (No votes)

See more:

How to write single query
to select data from server database insert into local database

i have used federated but its work only when internet is on
,my work is two database, one is on server and one is in localhost,and i have to insert many data entry work in day,
so before going to home i have to make dml staments into server Some times server to local host
how its is possible in asp.net

Posted 25-Aug-12 0:35am

madhu.gone

Updated 20-Jan-20 4:23am

v2

Add a Solution

Comments

[no name] 25-Aug-12 6:39am

Generally I use an editor and a keyboard to write queries.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-08-25T00:41:00

Solution 1

You can use federated tables to 'map' a table from a different MySql instance. After that, you can use the federated table in a INSERT INTO...SELECT... statement.

For information about federated tables, see How to Use FEDERATED Tables[^]

Few additional links:
- http://onlamp.com/pub/a/databases/2006/08/10/mysql-federated-tables.html[^]
- http://majid.mmxgroup.net/?p=150[^]
- http://www.mysqlfanboy.com/2010/07/federated-tables/[^]
- http://www.mysqlfaqs.net/mysql-faqs/Table-Types-or-Storage-Engines/Federated-Engine/How-to-create-federated-table-in-MySQL[^]

I think those should get you started...

Posted 25-Aug-12 0:41am

Wendelius

Updated 25-Aug-12 1:09am

v2

Comments

Mehdi Gholam 25-Aug-12 6:44am

5'ed

Wendelius 25-Aug-12 7:05am

Thanks :)

madhu.gone 25-Aug-12 7:05am

i read the above link but please can give any article link

Wendelius 25-Aug-12 7:10am

See the updated answer.

madhu.gone 25-Aug-12 7:31am

yes i am using above solution but i am getting following error
Unknown storage engine 'FEDERATED'

Wendelius 25-Aug-12 7:43am

The federated engine is disabled by default. Follow the instructions at http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

madhu.gone 25-Aug-12 7:57am

i have read above article its says that start server as
MySQL server binary using the --federated option
but please tell me how can i start
?

madhu.gone 25-Aug-12 9:05am

i have read above article its says that start server as
MySQL server binary using the --federated option
but please tell me how can i start
?

Wendelius 25-Aug-12 9:16am

One way to define the federated option is to modify the my.cnf and add the option:

[mysqld] 
federated

madhu.gone 25-Aug-12 9:29am

I AM NOT GETTING my.cnf FILE
but i am changing above modification in my.ini file,
but its not shown while doing show engines

madhu.gone 25-Aug-12 10:07am

yes its done by restarting the server

madhu.gone 25-Aug-12 10:07am

thanks for helping

Wendelius 25-Aug-12 10:23am

You're welcome :)

madhu.gone 27-Aug-12 1:58am

hi mike
i have used federated but its work only when internet is on
,my work is two database, one is on server and one is in localhost,and i have to insert many data entry work in day,
so before going to home i have to make dml staments into server Some times server to local host
how its is possible in asp.net

Wendelius 27-Aug-12 2:11am

I would suggest posting a new question about this since it's a different issue. This way also others see the post and can answer to it.

Espen Harlinn 25-Aug-12 8:42am

Nice :-D

Wendelius 25-Aug-12 9:16am

Thanks Espen :)

Mehdi Gholam · Accepted Answer · 2012-08-25T00:43:00

Solution 2

You can do the following:
Select on the source server to a file:

SQL

SELECT * FROM tablename INTO OUTFILE 'filename';

Import the file into your destination database:

SQL

LOAD DATA INFILE "filename" INTO TABLE tablename;

Posted 25-Aug-12 0:43am

Mehdi Gholam

Comments

Wendelius 25-Aug-12 7:06am

That would also work fine.

Espen Harlinn 25-Aug-12 8:42am

5'ed!

Mehdi Gholam 25-Aug-12 8:45am

Thanks Espen!