How can I transfer data to other database in SQLServer 2000!

Question

Hello, I have two Database, DB1 and DB2. How can I transfer data of DB1 specific table to DB2 in SQL Server 2000?

Answer 1

How about using DTS?

http://msdn.microsoft.com/en-us/library/cc917688.aspx

Answer 2

Right click on DB2 and Tasks-> Import will start the DTS Wizard. It's pretty simple.

Answer 3

Some additional context about this transfer would be useful. For example, is this a one-off transfer, a regular transfer, something you want to maintain for each transaction in DB1 or something else.

I'm going to assume this is a one-off or periodic occurance and, as such, I'd suggest Data Transformation Services (DTS) in SQL Server 2000:

http://msdn.microsoft.com/en-us/library/cc917688.aspx

Answer 4

If you need to do this through code (the .NET tag), then SqlBulkCopy is your friend -ideally when mixed with ExecuteReader on the source. Like so:

using (SqlConnection connSource = new SqlConnection(csSource)) // source db
using (SqlCommand cmd = connSource.CreateCommand())
using (SqlBulkCopy bcp = new SqlBulkCopy(csDest)) { // destination db
    bcp.DestinationTableName = "SomeTable"; // destination table
    cmd.CommandText = "SELECT * FROM [Foo]"; // source table
    cmd.CommandType = CommandType.Text;
    connSource.Open();
    using(SqlDataReader reader = cmd.ExecuteReader()) {
        bcp.WriteToServer(reader);
    }
}