CREATE PROCEDURE [dbo].[LoadDataFromFileIntoXXXXDB]

    @FilePath NVARCHAR(4000), --Ex. D:\test.xls

    @SheetName NVARCHAR(4000), --Ex. Sheet1

    @FileType  NVARCHAR(50), --Ex. Excel

    @TargetTableSchema SYSNAME, --Ex. dbo

    @TargetTableName SYSNAME --Ex. MyTable

AS

SET NOCOUNT ON

DECLARE @msg NVARCHAR(4000)

IF ISNULL(@FilePath,'') = ''

    RAISERROR('@FilePath can not be empty',16,1)

IF ISNULL(@SheetName,'') = ''

    RAISERROR('@SheetName can not be empty',16,1)

IF NOT EXISTS(SELECT * FROM [XXXX].sys.schemas WHERE name = @TargetTableSchema)

BEGIN

    SET @msg = REPLACE('Schema @TargetTableSchema can not be found','@TargetTableSchema',@TargetTableSchema)

    RAISERROR(@msg,16,1)

    RETURN

END

DECLARE @cmd NVARCHAR(MAX)

SET @cmd = '

IF OBJECT_ID(''[XXXX].['+@TargetTableSchema+'].['+@TargetTableName+']'') IS NOT NULL

DROP TABLE [XXXX].['+@TargetTableSchema+'].['+@TargetTableName+']'

EXEC(@cmd)

IF @FileType = 'Excel'

BEGIN

    SET @cmd = '

    SELECT * INTO [XXXX].['+@TargetTableSchema+'].[' + @TargetTableName + ']

    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''　

                    ,''Excel 12.0 Xml;HDR=YES;IMEX=1;Database='+@FilePath+'''

                    ,''SELECT * FROM ['+@SheetName+'$]'');

    '

END

--PRINT(@cmd)

EXEC (@cmd)

SET NOCOUNT OFF