Dynamic File Up loader

Overview:

                Easy tool to upload flat or .CSV files to the database table.  Very useful for small scale projects and does data dump quickly.

Query:

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id=OBJECT_ID(N'usp_Uploader'))

DROP PROCEDURE usp_Uploader

GO

SET   NOCOUNT     OFF;

GO

CREATE PROCEDURE usp_Uploader(

                                                @tableName nVARCHAR(20),

                                                @filePath nVARCHAR(50),

                                                @rowDelimiter VARCHAR(5),

                                                @fieldDelimiter VARCHAR(5),

                                                @startFrom VARCHAR(4),

                                                @errorMessage nVARCHAR(200) OUTPUT

                                          )

AS

BEGIN

      BEGIN TRANSACTION

      BEGIN TRY

            DECLARE     @query VARCHAR(max);

            SET         @query            =     'BULK INSERT '+@tableName+'

                                                FROM '+@filePath+'

                                                WITH

                                                (

                                                FIRSTROW ='+@startFrom+',

                                                FIELDTERMINATOR ='+@fieldDelimiter+',

                                                ROWTERMINATOR = '+@rowDelimiter+'

                                                )'                                 

            EXECUTE(@Query);

            COMMIT TRANSACTION

      END TRY

      BEGIN CATCH

            ROLLBACK TRANSACTION

            SET   @errorMessage = ERROR_MESSAGE()

      END CATCH

END;

GO

Usage:

DECLARE     @Error nVARCHAR(200);

EXEC usp_Uploader 'test','''d:\Source.txt''','''\n''','''\t''',100,@Error OUTPUT;

PRINT @Error;