Dare Devil Act (Khatron K Khiladi)

Bulk Upload of Excel in SQL SERVER

This Stored PRocedure takes the Uploaded Path of excel and inserts the fields in a table as defined.

CREATE PROCEDURE Procedure_Name
@ExcelPath text, @STATUSTEXT NVARCHAR(1) OUTPUT
AS
BEGIN
DECLARE @intErrorCode INT
SET NOCOUNT OFF;
BEGIN TRAN SP_TRAN
DECLARE
@service_provider varchar(300),
@path varchar(300),
@dbtype varchar(500),
@query varchar(300),
@datasource varchar(300)
set @service_provider='Microsoft.Jet.OLEDB.4.0'
set @dbtype='Excel 8.0;DATABASE='
set @path=@ExcelPath
set @query='select * from [Sheet1$]'
set @datasource=@dbtype+@path

EXEC('INSERT INTO Table_Name (Field1, Field2, Field3) SELECT convert(date, [Date],105), ltrim(rtrim(ExcelField1)) ,ltrim(rtrim(ExcelField2)) ,ltrim(rtrim(ExcelField3)) FROM OPENROWSET ('''+@service_provider+''', '''+@datasource+''', '''+@query+ ''') a')

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

COMMIT TRAN
SELECT @STATUSTEXT = '0'

PROBLEM:
IF (@intErrorCode <> 0)
BEGIN
SELECT @STATUSTEXT = '1'
ROLLBACK TRAN
END
SET NOCOUNT ON;
END