"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".
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.
This article has demonstrated how to use a bulk insert into a temporary table to create a standalone SQL ETL script.