Bulk Insert Issue...

[Scott Atkinson]

Hi Guys and Girls,

Tough one for me this, Im trying to bulk insert quite a big file, iv taken a small snippet of the file so you can slowly understand what im trying to get at! Please see below

24/06/2010 19:18:23 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)
24/06/2010 19:18:23 /IServerManager.Login (timings: authenticate=0 authorise=0 execute=4524029 logError=0 teardown=468003 total=4992032)
24/06/2010 19:20:31 /IServerManager.GetPublicKey (timings: authenticate=0 authorise=0 execute=0 logError=0 teardown=0 total=0)

I have created a new database within SQL Server with the column names as follows

CREATE TABLE [dbo].[MedWayFiles](
[RowID] [int] IDENTITY(1,1) NOT NULL,
[Date and Time] [datetime] NULL,
[Commands] [nvarchar](50) NULL,
[Timings] [nvarchar](50) NULL,
[Authenticate] [nvarchar](50) NULL,
[Authorise] [nvarchar](50) NULL,
[Execute] [nvarchar](50) NULL,
[LogError] [nvarchar](50) NULL,
[TearDown] [nvarchar](50) NULL,
[Total] [nvarchar](50) NULL,
[SLA Breach] [nvarchar](50) NULL,
CONSTRAINT [PK_MedWayFiles] PRIMARY KEY CLUSTERED

As you can see the column names reference words that are within the Text file! Can some one please help me identifying the keywords within the text-file and assigning them to the
Relevant column! i.e bulkinsert! As I have 47 Text-Files and they all hold about 300+ lines of information and doing them one by one, well we all know will take many years!!!

Thanks in Advance!

[Jerry Winston]

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

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.

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:

CREATE TABLE #LogEntry (Entry VARCHAR(MAX))

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

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.