Uploading Data from a Fixed-Width Flat File
By Kalman Toth, M.Phil., M.Phil., MCDBA

Prior to writing the stored procedure, I fired up the SSIS export/import wizard to export Sales.SpecialOffer in ragged-right format to a flat file SpecialOffer.txt .

The final destination of the data upload is #SpecialOffer temporary staging table. Since this is only a demonstration, the data is not inserted into Sales.SpecialOffer.  In a real-life situation, the data would be inserted to an actual table with a simple INSERT ….. SELECT…. The hard part of the task is to get the data into the staging table.

The first step is to stage the data line by line into the Sales.FlatFileData table using BULK INSERT. TRY-CATCH is used for error control. Most common error anticipated is file does not exist.

Once the data is in the line-by-line staging table, we have to convert the data into a real staging table which has the same design as the final destination table. SUBSTRING and the CONVERT functions are used to perform the data formatting.

The final

SELECT * FROM #SpecialOffer

Statement is only for demo purposes. In real life implementation probably would not be there, especially if we import a large dataset.

Here is the listing:

DROP PROC UploadSpecialOffers
GO
CREATE PROC UploadSpecialOffers @DataFileName sysname
AS
BEGIN

IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[FlatFileData]')
AND type in (N'U'))
CREATE TABLE Sales.FlatFileData (Line varchar (4000))

TRUNCATE TABLE Sales.FlatFileData

DECLARE @SQLCommand VARCHAR ( 1012 )

BEGIN TRY
SET @SQLCommand = 'Bulk INSERT Sales.FlatFileData FROM ''' + @DataFileName + ''' with
( TABLOCK ) '
PRINT @SQLCommand
EXEC ( @SQLCommand )
END TRY
BEGIN CATCH
RAISERROR( 'Error on Bulk Insert of flat file data into Sales.FlatFileData
table', 16, 1 )
RETURN 0
END CATCH

SELECT * INTO #SpecialOffer FROM Sales.SpecialOffer
TRUNCATE TABLE #SpecialOffer

INSERT INTO #SpecialOffer
([Description]
,[DiscountPct]
,[Type]
,[Category]
,[StartDate]
,[EndDate]
,[MinQty]
,[MaxQty]
,[rowguid]
,[ModifiedDate])
SELECT
SUBSTRING(Line,12,255)
,CONVERT(smallmoney, SUBSTRING(Line,267,22))
,SUBSTRING(Line, 289,50)
,SUBSTRING(Line, 339,50)
,CONVERT(datetime,SUBSTRING(Line,389,30))
,CONVERT(datetime,SUBSTRING(Line,419,30))
,CONVERT(int,SUBSTRING(Line, 449,11))
,CONVERT(int,SUBSTRING(Line, 460,11))
,CONVERT( uniqueidentifier,SUBSTRING(Line, 471,39))
,CONVERT(datetime,SUBSTRING(Line,510,30))
FROM Sales.FlatFileData

SELECT * FROM #SpecialOffer
RETURN 1

END
GO

EXEC UploadSpecialOffers 'F:\data\AdventureWorks\Export\SpecialOffer.txt'