Data import

Hi,

I do a monthly import in MSSQL 2K. The file contains 7.000.000 rows. These rows are loaded in to a special (preload) database. After the import several modifications are performed before it's uploaded to production.
The importfile is a comma-seperated file, fields are enclosed by double-quotes (seems to be an issue for BCP?)

If I'm right, there are 3 options to import:
1) data transformation
2) Bulk insert
3) BCP import

Could someone give me a more detailed explanation what the differences are between those methods? What's the fastest? disadvantages?

Thanks in advance!

1. DTS is more flexible, it can import/export from variety of sources (different RDBMS, Access, Excel, Text File, etc). You can also manipulate data during import/export.

2. BULK INSERT can only import and you can't do any data massage. But it is the fastest of all.

3. BCP can only work with text files and does not provide data manipulation.

You have two choices.
BULK INSERT and use stored procedure to manipulate data
or
Use DTS to import and manipulate data.

The first one may be faster.

I prefer DTS because, it is more secure and manageable.

I like DTS too, its features make it easier to create a data audit chain, which is the biggest plus to me.

Besides in a processing window pinch you can always call bulkinsert from a DTS package by using an ExecuteSQL step.

small (stubpid?) secondary question: It seems BCP has no facility for defining "fields enlosed by", which is available using in data import. You can define how fields are separated (FIELDTERMINATOR), but that's not what I'm looking for.
Am I missing something?

Do you mean quoted identifier? If so, use -q switch in bcp.