How do I copy records from one database to another?

views:

127

answers:

+1 Q:

How do I copy records from one database to another?

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?

+3 A:

Add linked server: Server Objects->Linked Servers
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

igor 2010-03-10 12:01:57

+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 2010-03-10 12:41:44

@Lieven, why such a minor edit?

KM 2010-03-10 12:42:38

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 2010-03-10 12:44:00

+1 for the answer

gyurisc 2010-03-10 12:44:22

@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

igor 2010-03-10 12:46:15

and developing the package is enough a huge task :). It is good for production and when the requirements are stable.

igor 2010-03-10 12:56:16

SQL Server Management Studio -> on database right mouse click -> Task -> Import /Export...

igor 2010-03-10 13:06:40

I have SQL Server Management Studio Express and I do not see this option. I guess this is the limitation of the product.

gyurisc 2010-03-10 13:10:22

SQL Server Management Studio Express 2005 or 2008?I am using Dev edition 2008.

igor 2010-03-10 13:47:21

SQL Server Management Studio 2005 Express

gyurisc 2010-03-10 14:14:40

you are right it (express) has no any import export wizardhttp://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/546f4407-0aa4-4b04-96f3-e6f0ba39a9d1

igor 2010-03-10 14:21:46

ansaurus

tags:

views:

answers:

How do I copy records from one database to another?

related questions