October 9, 2002 at 12:46 pm
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 28, 2002 at 6:31 pm
This was removed by the editor as SPAM
October 28, 2002 at 7:03 pm
Option 1)
My preferred method would be to use DTS. To summarize, here are the steps I would take:
Assumptions:
- All files to be loaded are in one directory
Steps:
- Create a Bulk Insert task that uses one of the files as a data source, and uses the specified format file. Choose the table you want to load. Go into the workflow properties and select "Disable Step".
- Create an Active Script task that uses the Scripting.FileSystemObject to iterate through all files in the directory. For each file in the directory, the ActiveScript task will do the following:
1. Set the current file to be the data source of Bulk Insert Task
2. Programmically execute the Bulk Insert Task
3. Get the next file until no files are left
Now when you execute your package, all your files will be loaded. Since BULK INSERT allows for shared table locks, you could design your package to load a number of files in sequence for faster performance.
Option 2)
Pure T-SQL approch. Execute the following:
CREATE TABLE #file_list (file_name varchar(512))
INSERT INTO #file_list
EXEC master..xp_cmdshell 'dir /B c:\yourdatadirectory'
DELETE FROM #file_list WHERE file_name IS NULL -- Get rid of blank lines (if any)
Now you could just write a query to concatenate a call to the bcp command like so:
CREATE TABLE #file_list (file_path varchar(512))
INSERT INTO #file_list
EXEC master..xp_cmdshell 'dir /B c:\'
DELETE FROM #file_list WHERE file_path IS NULL -- Get rid of blank lines (if any)
SELECT
'bcp pubs..authors in ' + file_path + ' -f formatfile -S Server -T'
FROM #file_list
Now you could save the script results to a .bat file and execute, or dynamically loop through temp table to call xp_cmdshell with the above string.
Enjoy!
Sincerely,
Mark Cudmore, MCP... On my way to MCDBA
Sincerely,
Mark Cudmore, MCDBA
November 1, 2002 at 3:03 pm
You could use the FOR command in DOS, this will loop though a list of files, and execute some dos commands to insert the data, bcp is only a little slower than BULK INSERT
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply