Help with Data Loading Options?

[Samways]

Hi,
I have experience of doing maintaining large Data Warehouse using Teradata and understand the various options available using that database. I am about to embark on the development of the ETL for a Data Warehouse that uses SQL Server 2000 and wondered if someone could advise me on the options I have available. Probably best if I give some scenarios:

a) Loading data into an empty table. With Teradata it would be 2 steps. The first would be a standard SQL Create Table statement run using the batch SQL utility (bteq) followed by the Teradata utility Fastload for loading into an empty table. I assume with SQL Server it is the same except I am using isql to run the Create Table and bcp to load the data.

b) Updating an existing table. Here it would depend on the number of inserts, deletes, updates etc. But the options are: (i) Use the utility MultiLoad to maintain an existing table. (ii) Create a new table. Run a number of SQL inserts to load this table, where one insert may be to laod from the exiting table records to be kept ignoring those to be deleted and those to be updated, the next insert may be to add the updated by joining the new records with the existing and applying the updates and the third insert would be to add the new inserts. Then drop the existing table and rename the new table to the old tables name.

I think these two scenarios covers the key questions I have. Can someone assist in pointing out what I should be considering and commenting on the problems I might face if I attempt to do thsi with SQL Server.

Regards

Rob

 

[moonshadow]

a) You can also use DTS, which is nice 'coz it's got a GUI, and probably just as fast as BCP, when using the Fastload option - you can also configure it to create the table for you. There's also the bulk insert command.
b) Not sure I quite understand what you're trying to do, but I guess you're trying to update the records in the table with data from a flat file. Your solution sounds a bit weird, and I've never tried anything like that before. Be careful about renaming tables - especially if you have referential integrity, stored procedures etc.