This post is the first part of a multi-part series. First we will look at what we need to do then the next post will show the solution.

For a very simple example, please jump to Part 5.

Requirements

We have a text file coming in that has information that we need to insert into a table. The text file is a report, but it is not a CSV file and is not delimited in any way. However, the data we need is always in the same place for each piece of data we need. The data has “header” information as well as detail information.

To get the job done we need to use two tables at the minimum. We use three tables in this solution because we want another table as a history table of audit information. We also make use of a temporary table in one of the stored procedures.

Here is the input file.

Report Header from XYZ Inc.

Filename: 123.txt
Source: XYZ Inc.
Four-digit code number: 5578

Below are the transactions
Transaction number: 12345   Transaction Amount:    12000
Transaction number: 12346   Transaction Amount:   770000

End of Report

We want to have the data we need into a table that looks like this.

We will have three SQL Server tables.

• dbo.InReport – we will bulk insert into this table (has one column: [InRows])
• dbo.InReportHistory – an auditing table
• dbo.Reports – has the final results, as shown above in the screen shot

We will use five stored procedures to achieve our goals. One of the five simply calls the other four. Here is part of the stored procedure that calls the other four.

	EXEC dbo.DeleteRowsInReport  -- delete rows in table InReport
	EXEC dbo.BulkInsertFile -- import from file to table InReport (one column: InRows)
	EXEC dbo.CopyInRepToInRepHist -- copy to provide an audit trail
	EXEC dbo.FromInReportToReports -- extract data into table dbo.Reports

The next post shows the code used in the stored procedures.