Have Geek Brain

How to transfer data to another database in SQLServer 2000!

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

+1


a source to share


4 answers


How about using DTS?



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

+1


a source


If you need to do it with code (.NET tag) then SqlBulkCopy

is your friend when you mix it with ExecuteReader

in 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);
    }
}
+1


a source


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

0


a source


Some additional context for this transfer would be helpful. For example, this is a one-time transfer, a regular transfer, what you want to store for each transaction in DB1, or whatever.

I'm going to assume that this is a one-time or recurring event, and so I suggest Data Transformation Services (DTS) in SQL Server 2000:

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

0


a source






More articles:


All Articles