Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    23

    Unanswered: Bulk inserts between two different DBMS

    Hi folks,
    I have a table located in DB2 nd I need to have a mirror image of this table on a SQL2000 database to avoid some server downtime problems.
    Right now I have a solution using ADO.NET with Windows Services.

    This windows service invokes itself everyday morning and pulls all the records from this table in DB2 to a dataset. Then I loop through the dataset and insert every record into SQL 2000 Table. This method is working fine ( It take approximately 2 minutes to insert 5000 records). I am just wondering whether there is any way to acheive bulk insertion in this case. Considering future growth of table I am not thinking the existing solution is neither elegant nor efficient.

    Please let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)

    Thanks,
    Sai

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by saikrishna_dv
    Please let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)
    You probably want to set up a DTS package in SQL Server that migrates the data. This does support using another DBMS as a source.

    The alternative is you dump from DB2 to a flat file and then BULK INSERT that flat file into SQL Server.

    Both approaches will be much faster than a procedural row by row transfer on large data sets.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •