Copy data between 2 SQL servers

Using the BCP utility that comes with SQL Server 2000/2005 you can quickly export data from one SQL server installation and import it into another. This is described on MSDN.

The tricky thing is to get the command line parameters right, you need to specify the -S argument if you have multiple or named instances of SQL server installed.
Example:
bcp Northwind.dbo.Authors out C:\Temp\DataExport.dat -n -T -S MPDESKTOP\SQL2005

Otherwise you will get stupid errors such as: "NativeError = 18456 .... Login failed for user xxxx" ...

Then the import is pretty simple, you can use Query Analyzer (or SQL server Management Studio) to run this query:

USE Northwind
GO
BULK INSERT Authors
FROM 'G:\Transfer\Tempfiles\DataExport.dat'
WITH (DATAFILETYPE='native');
GO