How to do bulk insert?

[celine7822]

I have a lot of dbf files that needed to be uploaded to the mssql. For all the while, I have been doing the insert record by record until my db admin feedback to me that db log file has grown far too big.

I have also found that by doing a bulk insert will solve the problem. But I do not know how to go about using it. Can someone help?

Thanks!

 

[Mike Lewis]

Celine,

You might find it easier to use the Data Transformation Services (DTS) utility. Essentially, this is a wrapper for BULK INSERT.

Launch DTS from the Start menu or from Enterprise Manager (select Import/Export data). Step through the wizard, selecting your tables and columns. When you get to the final step, specify that you want to save a "package". This package is essentially the BULK INSERT and other commands needed to create the tables. You can run the package at any time.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[celine7822]

Encountered the below error when trying to select colums screen.

"Error Source: Microsoft JET Database Engine

Error DEscription: The Microsoft Jet database engine could not find the object 'file_a'. Make sure the object exists and that you spell its name and the path name correctly.

Context: Error calling GetRowset to get Schema info. Your provider does not support all the interfaces/methods required by DTS."

 

[robsuttonjr]

You need to specify the appropriate dsn/foxpro dbf driver or whatever matches the tables you are using when setting up dts. Why would you get a jet error if you are using .dbf to sql server?

 

[celine7822]

Ok now that i am able to proceed on, while the dts is trying to query and insert data, it has encountered this error, "Cannot insert the value NULL into column ID"

My column ID is an identity field, with running number.

 

[Mike Lewis]

celine7822,

My column ID is an identity field, with running number.

The identity column must be omitted from the column list in DTS or the bulk insert. You cannot insert any value into an indentity column -- just leave it out.

Mike



Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk