INSERT into one db SELECT results from another db

[LonAllen]

Is there a way to INSERT the data from the resultSet of a SELECT statement from one Database to another?

I have two databases (currently on the same server, but will be on separate servers in production) that I want to be able to query one for data to be inserted into tables on the other. At the moment the only way I see to do it is to loop through the resultSet from the SELECT query and build a new INSERT statement to put the data in the second database. I was hoping some of you have done this more efficiently.

I am using mysql for my database servers.

thanks,

[raviparthan]

i HAVE TWO EXAMPLES FOR THIS.. TRY



INSERT INTO TABLE1
SELECT * FROM TABLE2
WHERE SOME CONDITION HERE


SELECT COLUMN1
INTO
dbo.TABLE1
FROM
TABLE A,TABLE T
WHERE T.Time_ID = A.Time_ID
ORDER BY
A.Unit DESC

[skhanal]

You can refer to another database using

databasename.objectowner.tablename syntax.

If the database is in a separate server, then you have to create a linked server to the other server and use four part naming convetion to refer to the table as

linkedservername.databasename.objectowner.tablenam e