SQL Server ->> 自动创建表并从文件加载数据

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