August 21, 2004 at 3:00 pm
Hello,
how can I fill a table in my DB by importing data from a textfile? the online documentation is confusing me a little bit. My Problem is due to the fact that I don't know how to define my target. Which driver should I choose? I would like exactly the steps to fill the table.
Thanks for helping.
August 22, 2004 at 11:19 pm
You do have at least several choices here:
1. DTS, bcp record, bcp record and fields
with bcp you specified each record delimiter and field delimiter (SQL still uses this utility internaly to export and import data tables to and from disk file (flat file))
if you leave out field delimiter, you can have a record string of all fields and load into table with one field for entire record (this is powerful way of loading fields, because fields can be blank or of invalid types when loading) and all fields can be interputed during each parse of a record, and many records can read, stored, and tested prior to being used in updating other tables and fields before it is also used as a data element itself.
Just like loading flat files into meaningful data files with complex relations, during which a stored procedure can process multiple data sets.
I still use this method today for importing from foreign databases (with different table designs) and from many flat files (of old tables), and input the data while mapping the import of these outdated database designs into the design of our (current tables).
Any newly aquired companies data and and an entire database can be imported into a single, yet combined database within a few days of importing through this method, which includes a ton of business rules (per records and fields).
SeeBeyond.com still uses this method (my concept, and design hidden inside) and calls it eLoad (of course it now uses a powerful front-end GUI) for cut and paste matching of the import tables and fields between the (newly aquired database into the combined database) (and allows simple basic to add the business rules behind each record and field) and this functionallity costs at least a 100,000 dollars to own, and works with any database, and/or operating system.
Yes check it out, and still no royalties. But I was paid independent consultant wages at the time!
Coach James
August 23, 2004 at 12:06 am
Also, the BULK INSERT T-SQL command can be called from sql code, and it's as useful as bcp for importing files. In fact, it's faster than bcp because it lives inside sql server, and is not an external tool. Use BCP to create the format file if needed, but if your data is tab delimited columns, your already good to go.
BULK INSERT in books online.
Julian Kuiters
juliankuiters.id.au
August 23, 2004 at 4:30 am
August 24, 2004 at 11:07 am
I have found that DTS is much more capable than BCP or BULK INSERT. I've done lots of importing of comma delimited text files, and BCP/BULK INSERT all have issues when the data contains the delimiter (ex: importing comma separated fields and trying to load data in LASTNAME, FIRSTNAME format as a single column).
DTS seems to much better understand delimiters (other than commas, too) and can even allow you to "preview" the source data. DTS also gives much greater flexibility with mapping the imported data to target columns, and allows you to manipulate the data on it's way in.
Additionally, DTS allows you to configure multiple imports to occur simultaneously.
Once the DTS job is configured, you can right-click it and schedule it to run as a Scheduled Job by SQL Server Agent.
-- Mitch
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply