469,344 Members | 6,274 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Bulk Insert Issue...

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!
Aug 10 '10 #1
1 1367
Jerry Winston
145 Expert 100+
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #myLogFiles(LogEntryFile VARCHAR(MAX))
  2.  
  3. BULK INSERT #myLogFiles 'C:\data\myLogFileList.txt
  4. WITH
  5. (ROWTERMINATOR = '\n')
  6.  
  7. SELECT 'BULK INSERT [#LogEntry] ''' +
  8. LogEntryFile +''' WITH (ROWTERMINATOR = ''\n'')'
  9. FROM #myLogFiles
  10.  
From SSMS copy the code from the grid view below this code into a new query window:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #LogEntry (Entry VARCHAR(MAX))
Insert your parsing code below your BULK INSERT statements in the new window:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [MedWayFiles]
  2. SELECT
  3. SUBSTRING
  4. (Entry,
  5. CHARINDEX('attribute1',Entry),
  6. CHARINDEX('attribute2',Entry)-CHARINDEX('attribute1',Entry)
  7. ),
  8. ...
  9. FROM
  10. #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.
Aug 10 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by php newbie | last post: by
6 posts views Thread by pk | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.