Thursday, May 5, 2011

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

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

From stackoverflow
  • How about using DTS?

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

  • 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:

  • 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;
        using(SqlDataReader reader = cmd.ExecuteReader()) {


Post a Comment