Bulk Insert Issue...

**Jerry Winston** · Aug 10 '10, 03:32 PM

This is doable. Just use BULK INSERT with ROWTERMINATOR like '\n' to access log entries one row at a time. Parse the log entry using CHARINDEX, SUBSTRING, and CAST inserting the derived values into your table [medWayFiles].

Once you perfect parsing the log strings, you can write a script to create the other 300 scripts. If your file names are numbered ie 'log0001.txt',' log0002.txt' the potential to create a loop to generate the BULK INSERT functions are obvious. If they is no pattern to the file names, use

Code:

dir /b *.[myLogFileExtension] > myLogFileList.txt

from the windows command shell to create a list of file names.

This code creates a list of BULK INSERT statements you can copy from the file list created using the windows command line.

Code:

CREATE TABLE #myLogFiles(LogEntryFile VARCHAR(MAX))

BULK INSERT #myLogFiles 'C:\data\myLogFileList.txt
WITH
(ROWTERMINATOR = '\n')

SELECT 'BULK INSERT [#LogEntry] ''' +
LogEntryFile +''' WITH (ROWTERMINATOR = ''\n'')'
FROM #myLogFiles

From SSMS copy the code from the grid view below this code into a new query window:

Code:

CREATE TABLE #LogEntry (Entry VARCHAR(MAX))

Insert your parsing code below your BULK INSERT statements in the new window:

Code:

INSERT INTO [MedWayFiles]
SELECT
SUBSTRING
(Entry,
CHARINDEX('attribute1',Entry),
CHARINDEX('attribute2',Entry)-CHARINDEX('attribute1',Entry)
),
...
FROM
#LogEntry

It's not elegant, but it'll get the job done. An elegant solution IMO would do this all in one punch making use of sp_executesql, CURSORs, and CTE's.

Bulk Insert Issue...

Bulk Insert Issue...

Comment