Copy tables from SQL Express 2005 to SQL 2005 server

We have a vendor machine on our network which is at a remote location and I would like to copy about 15 tables from that database to our location database so I can build reports using data from our local database and the remote database tables. The machine is not on our domain but is on our network. I have made it possible to connect to the remote database through settings on the remote database and created a user that has admin credentials. I would like to copy the data from these tables on a weekly basis and at the end of the month truncate all the data and start with an empty database for the next month.

What is the best way to accomplish this? I believe I will need to create a Stored Procedure to do this but want to be sure.

Thank you in advance for your help.
TimSr. System AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
You can use import and export wizard and save the package. You can schedule this package at monthly schedule time. Truncation of existing data is configurable in the package.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RayData AnalystCommented:
A different approach would simply be to add the remote database as a linked server if it is always online.  You can then directly query those tables and save the monthly transfer of data.  If you're copying tables/data over to the local server because the remote is sometime not connected to the network, then disregard my comment :-)

Here's a pretty good description of creating a linked server.
http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are both instances SQL Express?
0
TimSr. System AdminAuthor Commented:
Thanks for all the feedback. This answer turned out to be the best course to take.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.