Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL-only ETL using a bulk insert into a temporary table (SQL Spackle)

By Stan Kulp,

"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
...into a SQL Server Management Studio query editor window and execute it..

...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.

 

Resources:

sp500hst.zip
Total article views: 8495 | Views in the last 30 days: 4
 
Related Articles
FORUM

Try Catch

Try Catch with @@Transcount

FORUM

Begin Tran...Commit Tran with Exec SQL Insert Cmd

BEGIN TRAN, COMMIT TRAN, ROLLBACK

FORUM

Try / catch info needed.

possibly an misunderstandin of try /catch exceptions.

FORUM

Try/Catch RaisError Question

Try/Catch RaisError Question

FORUM

Working with CATCH Block continuity

Working with CATCH Block continuity

Tags
bulk insert    
etl    
sql spackle    
 
Contribute