November 21, 2002
Oracle v SQL Server, Part 2
My next challenge was to load some data into a table from a flat file (in this case a comma delimited file). In Oracle you create the table in your SQL tool of choice and then load the data using SQL*Loader.
You can do this in SQL Server too. Create the table in SQL Query Analyzer and then load your flat file using bcp. But this is Windows, so there must be a wacky visual doo-hicky to do the job for you, and there is. Say hello to Data Transformation Services, a.k.a DTS. Which is a wizard. Which is nice.
I like DTS, but I have found one drawback. If you load data from a flat file it does not allow you to specify which table to load it into. It just uses the name of the source data file as the name of the table to load the data into.
This caught me out the first time I used it but I soon adapted to the new world order. If the table already exists in the database you specify then DTS dutifully tries to load the data into it. Obviously if the structure of the table is wrong you get errors, but this method does allow me to pre-create my tables. I just have to rename my data files.
Posted by Andy Todd at November 21, 2002 01:40 PM