October 9, 2002 at 11:38 am
Is there a way to bulk insert multiple files in which I will be using the same format file? Typing out each one wouldn't be a solution. Talking about hundreds of files.
Any help would be appreciated thanks
October 9, 2002 at 1:50 pm
Are those files be well organized? For example, Are they in same directory, has same format/structure, file name contains date info. Is this a one time process? What type of files they are?
October 9, 2002 at 2:02 pm
They all reside in the same dir. Exactly same format. I have created an .fmt to load one but now they just told me they need all the files loaded back from Jan 1 and there is a separate file for each day. This will possibly turn into an ongoing process. They are just regular ms-dos files fixed length according to my .fmt file. Now they just gave me a new wrinkle. They want the date on each record for respective file. Example
file name: 01012002.txt
01022002.txt.
Is there anway also to extract the file name and store it in a column in SQL?
October 9, 2002 at 2:29 pm
Two options.
1. Create DTS package to load the data. Search http://www.sqldts.com article "Looping, Importing and Archiving" for sample.
2. Use xp_cmdshell to load all data files name into a table and perform BULK Insert for each records by looping the table.
October 10, 2002 at 5:32 am
Consider using a vbs script with SQLDMO objects. Create a loop for each file, to import the file to a temp table, then run an insert query to load the records from the temp file to the permanent table and add the date from the filename.
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply