Import Data from CSV file to Microsoft SQL Server 2008 using stored procedure

Write the following procedure in Microsoft SQL server and Enjoy....

--Exec spImportToCSV 'e:\input.csv'

CREATE PROC spImportToCSV

@FilePath varchar(100)

AS

BEGIN

CREATE TABLE #tmpCSV

(

      ID INT,

      FirstName VARCHAR(100)

)

DECLARE @Sql varchar(max);

SET @Sql= 'BULK INSERT #tmpCSV FROM '''+@FilePath+''' WITH(

FIRSTROW = 2,FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

print @Sql

EXEC (@Sql)

--FIRSTROW SET starting  ROW

--FIELDTERMINATOR SET field terminator

--ROWTERMINATOR SET row terminator

--Check the content of the table.

SELECT * FROM #tmpCSV

--Drop the table to clean up database.

DROP TABLE #tmpCSV

END