Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 7786 | Views in the last 30 days: 225
 
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones