"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Introduction

The BULK INSERT statement imports a data file into a database table or view in a user-specified format in SQL Server 2005 and above. The interesting thing about the bulk insert statement is that it provides a means of performing a complete Extract, Transform and Load (ETL) operation entirely within SQL.

A SQL ETL script

The following SQL code creates a SQL Server table named SP500 and a temporary table named #temp, bulk inserts the file sp500hst.txt into the #temp table, then inserts selected records from the temporary table into the SP500 SQL Server table.

Download the attached sp500hst.zip resource file and extract the sp500hst.txt file it contains to the C:\testfiles\ folder.

Copy this SQL code...

BEGIN TRY
    DROP TABLE SP500
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
    DROP TABLE #temp
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE SP500
    ([Date] varchar(50)
    ,[Ticker] varchar(50)
    ,[Open] varchar(50)
    ,[High] varchar(50)
    ,[Low] varchar(50)
    ,[Close] varchar(50)
    ,[Volume] varchar(50))
CREATE TABLE #temp
    ([Date] varchar(50)
    ,[Ticker] varchar(50)
    ,[Open] varchar(50)
    ,[High] varchar(50)
    ,[Low] varchar(50)
    ,[Close] varchar(50)
    ,[Volume] varchar(50))
BULK INSERT #temp
   FROM 'C:\testfiles\sp500hst.txt'
   WITH
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR = '\n',
         FIRSTROW = 1
      )
      
INSERT INTO [dbo].[SP500]
SELECT * FROM #temp
WHERE [Date] = '20100504'
ORDER BY [Ticker]
SELECT * FROM [dbo].[SP500]
DROP TABLE #temp

...to observe that the selected data has been inserted into the SP500 table.

Conclusion

This article has demonstrated how to use a bulk insert into a temporary table to create a standalone SQL ETL script.