1

I need to pull data from one SQL 2005 Express database to another and I need to do this periodically. It is not a straight copy from one table to another, but I would use different views from the source table. I also need to do this periodically.

My first idea is to write a small application in C# and run it somehow in every hour or so, but I would like to keep this data manipulation logic as close to the database server as possible.

What are the tools and features that I can use SQL Server 2005 Express?

gyurisc
  • 11,234
  • 16
  • 68
  • 102

1 Answers1

3
  1. Add linked server: Server Objects->Linked Servers
  2. a) Write script to copy (you don't prefer this method, I think)
    b) Create package SSIS in VS and execute it with different parameters (you can pass input parameters to package)
    c) Use wizard (Import/Export) than save this task as package (as file to edit or to server to execute)

I think c) is your case.

EXPRESS has no SQL Server Agent, so use other server (enterprise or standard editions) to execute your package periodically

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
garik
  • 5,669
  • 5
  • 30
  • 42
  • 1
    +1, I like 2.a, you can make a stored procedure with multiple INSERTS to hit all of your tables: `INSERT INTO server1.db1.dbo.YourTable VALUES (col1, col2,...) SELECT col1, col2,... FROM server2.db2.dbo.YourTable WHERE ...` – KM. Mar 10 '10 at 12:41
  • Thanks for the suggestion. I will check it out and see if I go with B ro C option. You are correct, C will be enough for me. How do I start the Import Export wizard in 2005? I am familiar with 2008 and there is a separate application there. – gyurisc Mar 10 '10 at 12:44
  • @KM Because, author of question does not prefer this method. I guess this is a good enough case to do this, so I added it to answer – garik Mar 10 '10 at 12:46
  • and developing the package is enough a huge task :). It is good for production and when the requirements are stable. – garik Mar 10 '10 at 12:56
  • 1
    SQL Server Management Studio -> on database right mouse click -> Task -> Import /Export... – garik Mar 10 '10 at 13:06
  • I have SQL Server Management Studio Express and I do not see this option. I guess this is the limitation of the product. – gyurisc Mar 10 '10 at 13:10
  • SQL Server Management Studio Express 2005 or 2008? I am using Dev edition 2008. – garik Mar 10 '10 at 13:47
  • SQL Server Management Studio 2005 Express – gyurisc Mar 10 '10 at 14:14
  • 1
    you are right it (express) has no any import export wizard http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/546f4407-0aa4-4b04-96f3-e6f0ba39a9d1 – garik Mar 10 '10 at 14:21