Importing data from a text file into SQL Server:
The text file is stored in ascii format. It has three pieces of information. The first is the report number, the second is the line number and the third is the actual report. The problem we face is that for every report number, the data is stored in line numbers with the same report number. For example
Report_num Line_num Report
650000 1 Samaritan hospital
650000 5 397-22-1234
650000 8 ------------------
--------- n ------------------
We not only want to import this data into an SQL Server table but we want the format in such a way that for every report number, we just have one report and no line number.
STEP 1:
As the data is in ASCII format, we can use our favorite bcp command to import it into an SQL table
Souce location: F:\kashef\archive_table_pre.txt
Destination location: ACOM\testDB.archive_table_pre
The syntax of the command we used was
bcp testDB.dbo.archive_table_pre in F:\kashef\archive_table_pre.txt /Usa /S ACOM /f C:\bcp.fmt
The rows were copied successfully into archive_table_pre
STEP 2:
The second step involved writing a stored procedure that would involved two while-loops to process the data in the right format. The outer loop will loop through all the report numbers one by one and find the maximum number of line numbers. Once we get the max line number, then the inner loop starts. While the report number stays the same, the loop assigns the comment for every line to a pointer variable. This pointer variable is then assigned to a VARCHAR variable which is then appended to the text field in table report_table. If none of this is making sense to you, please look at the code.
DECLARE
@I INT,@J INT,@K INT,
@ptrval VARBINARY(16),@com VARCHAR(255)
SELECT @1=650000
SELECT @J=1
WHILE @I<=650020 /* OUTER LOOP
BEGIN
INSERT INTO report_table VALUES(@I,'report')
SELECT @K=MAX(line_num) FROM report_1 WHERE @I=report_num
WHILE @J<=@K /*INNER LOOP
BEGIN
SELECT @ptrval=TEXTPTR(report)FROM report_tableWHERE report_num=@I
SELECT @com=(SELECT report FROM report_1 WHERE @J=line_num and@I=report_num)
UPDATETEXT report_table.report @ptrval NULL 0 @com
END
SELECT @com='' SELECT @J=1 SELECT @K=1 SELECT @I=@I+1
END
Comments: