For any DBMS system (I know that it sounds redundant) data can come into variety of forms and formats .. say CSV, XML, another DBMS, tab limited file or fixed position file or any proprietary format (as long as you have data providers for them ). And most of connected DBMS systems have different approaches to import those kind of formats into database. One of that format is fixed position files, in many cases these finds of files gets generated by applications that uses flat file databases to store data.
In SQL Server there are three main ways to import these fixed position files into database. Using Bulk Insert tsql command, using BCP utility or using SSIS. In today’s post I will be showing way to import file using BULK INSERT. May be later on if I am feeling graceful, I will write about SSIS and BCP methods as well . Ok, so for purposes of test, I have created a fixed position file using SSIS. If you don’t know how it is done is SSIS see this previous post for HOW TO create fixed width file in SSIS.
Next, I’ve created a simple Bulk Insert script which reads that file and stores it into some temp table and then parse that file and inserts into table.
USE [tempdb] GO -- Create temp tables CREATE TABLE [dbo].[tableTest]( [FirstName] [NVARCHAR](50) NOT NULL, [LastName] [NVARCHAR](50) NOT NULL, [AddressLine1] [NVARCHAR](60) NOT NULL, [City] [NVARCHAR](30) NOT NULL, [PostalCode] [NVARCHAR](15) NOT NULL ) GO CREATE TABLE #tempData (FromFile NVARCHAR(200)) BULK INSERT #tempData FROM 'D:\Temp\samples\Output.txt' -- Path of Data file WITH ( ROWTERMINATOR ='\n' -- According to MSDN, New Line Feed (\n) automatically adds Carrige Return (\r) ) DECLARE @FromFile VARCHAR(200) DECLARE parseData CURSOR FOR SELECT FromFile FROM #tempData OPEN parseData FETCH NEXT FROM parseData INTO @FromFile WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO tableTest VALUES(SUBSTRING(@fromFile,1,50),SUBSTRING(@fromFile,51,50),SUBSTRING(@fromFile,101,60),SUBSTRING(@fromFile,161,30),SUBSTRING(@fromFile,191,15)) FETCH NEXT FROM parseData INTO @FromFile END CLOSE parseData DEALLOCATE parseData -- Verifying results SELECT * FROM tableTest -- House cleaning DROP TABLE #tempData DROP TABLE tableTest
May be this query is not designed based on best practices but I personally liked this approach better than one from SSIS because since it is just a TSQL script it is more easy to edit at any point without use of any additional tool like SSMS (or Visual Studio in case of SSIS)
That’s it for now.
It’s Just A Thought …