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.
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 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. File name: 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.