How Do I Update A Table From A Different SQL Server

I have a table in a database on my development(local workstation) SQL Server 2008 Express that I have added some records to. Now, I would like to deploy this table to the production SQL Server 2000 located on a server. What is the best was to accomplish this? I want to retain the data from the development instance. Both tables have the exact same structure. Can I use the DTS Import/Export wizard from the production SQL Server 2000 through the SQL Server Enterprise Manager? Does it overwrite tables when importing? Will it h开发者_如何转开发andle the different versions of SQL Server?

One technique is to create a linked server. Check the msdn link Linked server - msdn

Then you can write a single query to do the Update or whatever that has access to tables on both servers.

You development work should result in scripts, not in bits in the database. There are many advantages in doing so. When you deploy, you run the developed scripts on the productions database. Now that you let the cat out of the bag, your best option is to use export the table and then import it, see FAQ: How do I import/export data with SQL Express. SQL's own import/export wizard requires SSIS (see Importing and Exporting Data by Using the SQL Server Import and Export Wizard), which is not part of Express editions.

If the table is reasonable small you can use a distributed query (ie. linked server), but that will require MSDTC because it will be an update, and you may need to configure it.

If you have SqlServer Management Studio (not express). You can use the import and export options to quickly transfer data in and out. This assumes you can connect to the database from your local machine though.