I have an access 2000 database that contains 100,000 records that I need to import into a sql 2000 database which currently holds 2,500,000 records. I have been looking at various ways of doing this and I think the bulk insert is the way to go (I think this will not expand the transaction log that much as I am short of space).
I have exported the access 2000 data into a text file (fixed width with tab separating the values). The only difference between the access 2000 data and the sql data is that the sql table has an extra field which is the primary key and which is an identity seed. I have been trying to create a new package but to no avail. In the designer I have added the connection to the sql table and I have added the bulk insert command and have pointed it to the necessary text file but it does not work. I think that I may have to create a format file but I do not know how to do this, does anyone have an advice that they can give me. Thanks
