Copying Tables to DB on Another Server

[JohnBates]

Hi all -

Periodically, I need to "refresh" the data that is on my Test server.

I have been using Enterprise Manager's Export tool to copy
certain tables "manually" from my Production server.

My question: How can I automate this within a stored procedure?

I want to select all fields, all rows from the named table.

Would the T-SQL look something like this...?
INSERT INTO Test_Server.frigdare.dbo.LoadId
SELECT *
FROM Production_Server.frigdare.dbo.LoadId



Is it even possible to name the Server within the INSERT statement?

Thanks for any advice.

John

 

[TomSark]

If it's going to another server, then set up a linked server, then the insert/select will work: you need to issue the fully qualified paths of all objects when you reference a linked server.

You could also set up a DTS package that does the transfer as well...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[foxdev]

John, when you were using the Export Wizard you may have noticed that one of the last steps includes the box "Run immediately"; on that same screen there is an option to Save, allowing you to save it as a DTS job. Once saved, it can be scheduled or run manually without having to go through all the steps again.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[Guest_imported]

Hi guys,

I have the questions and I really have no idea what happen and how to solve this. question 1: I have the tables in sql 6.5, I used acces 97/200 to link table(get external data) from sql 6.5 to access. After that, i check the table in access(the one i just linked) and i discover that I have a lots of rows contain #Deleted, then I go back to sql table and check it, and i don't have this problem. Any idea why?, please help

Question 2: After i imported some tables from sql 6.5 to access 2000, i ran the query to retrieve something, then i have the diffrent results in sql 6.5 and access 2000 even thought i used the same query. I was loosing some data in sql 6.5 but not in access, even though i used same query, tables. any help please?

thank you