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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy