[DataBase][SQL Server]T-SQL动态语句导入Excel数据

缘由: 要导入一批数据, 为一年12个月的工资数据, Excel格式一致. 下面采用T-SQL动态语句来实现. 耗时比单独导入每个Excel表格数据要快很多. 拿我的这次导入的数据为例, 每个Excel导入需要大约20s以上, 而用动态语句将所有数据导入到全局临时表, 再处理, 只需要4s!

 

下面是一些我认为有用的T-SQL语句(其他无关语句已删除, 如果有错漏请指正, 因删除后无重新编译):

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[IMPORT_临工工资]    Script Date: 01/18/2011 14:56:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IMPORT_临工工资]
--注意: 用到的是全局临时表
--为什么要用全局临时表? 因为采用动态语句的方式相当于两个连接? 所以后面exec动态语句后并不能得到临时表的数据
--会提示临时表不存在. 至于在同个交易下可行不可行未测试
AS 
    BEGIN
-------------------------------------------------------------------
--变量声明

--Excel路径可以作为参数传入
--        DECLARE @SHEET_NAME VARCHAR(256)
--        SET @SHEET_NAME

-------------------------------------------------------------------
--判断全局临时表是否存在, 注意全局临时表命名不要太普通
        IF EXISTS ( SELECT  *
                    FROM    tempdb.dbo.sysobjects
                    WHERE   id = OBJECT_ID(N'TEMPDB..##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A')
                            AND TYPE = 'U' ) 
            BEGIN
                TRUNCATE TABLE ##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A    
                DROP TABLE ##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A
            END

        DECLARE @s VARCHAR(MAX)
        DECLARE @s1 VARCHAR(MAX)

        DECLARE @I INT --循环变量
        SET @I = 0

--循环拼SQL
        WHILE @I < 12
            BEGIN
                SET @I = @I + 1
                DECLARE @NOTE VARCHAR(255)
                SET @NOTE = '2010年' + CAST(@I AS VARCHAR) + '月临工工资'

--注意: 单引号在拼SQL时需要特殊处理
                SET @s1 = '
            SELECT  [2010年' + CAST(@I AS VARCHAR) + '月临工工资] AS 代号 ,' + ''''
                    + '2010-' + CAST(@I AS VARCHAR) + '-1' + '''' + ' AS 月份, '
                    + '
                F2 AS 姓名 ,
                F3 AS 单位 ,
                F17 AS 应发数 ,' + '''' + @NOTE + '''' + ' AS 备注
        FROM    OPENDATASOURCE(' + '''' + 'MICROSOFT.ACE.OLEDB.12.0' + ''''
                    + ',' + '''' + 'Data Source=D:\2010年'
                    + CAST(@I AS VARCHAR)
                    + '月份临工工资.xls;Extended Properties=Excel 5.0' + ''''
                    + ')...[汇总明细表$] A ' + '
                    '
                IF ( @I > 1 ) 
                    SET @s = @s + ' UNION ' + @s1
                ELSE 
                    SET @s = @s1
            END  

        SET @s = 'SELECT  
                    月份 , 
                    代号 ,
                    姓名 ,
                    单位 ,
                    备注
                INTO    ##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A FROM ('
            + @s + '
        ) T
        '

--PRINT是调试动态语句的有效方法
        --PRINT ( @s )

--执行拼好的SQL, 使得数据导入到全局临时表
        EXEC (@s)

/*
--注意1: 像下面这种直接执行的语句, OPENDATASOURCE第二个参数不能实用变量!
--注意2: 执行OPENDATASOURCE时需要确保要导入的Excel已关闭(EXCEL2010采用只读方式打开的话, 好像不会报错, 编辑模式会), 否则报错.
　　　　SELECT  [2010年1月临工工资] AS 代号 ,
                F2 AS 姓名 ,
                F3 AS 单位 ,
                F17 AS 应发数 
        INTO    #TEMP_临工雇员
        FROM    OPENDATASOURCE('MICROSOFT.ACE.OLEDB.12.0',
                               'Data Source=D:\2010年1月份临工工资.xls;Extended Properties=Excel 5.0')...[汇总明细表$] A
*/
-------------------------------------------------------------------


        SELECT    月份 AS PROVIDE_DATE , 
                            姓名 AS DISPLAY_NAME ,
                            ISNULL(H.PERSON_CODE,
                                   ISNULL(Z.PERSON_CODE,
                                          ISNULL(GA.PERSON_CODE,
                                                 LA.PERSON_CODE))) AS PERSON_CODE ,--代号
                            单位 AS PROVIDE_DEPT ,
                            应发数 AS SALARY_AMOUNT ,
                            备注 AS SOURCE 
        FROM      ##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A A  WITH ( NOLOCK )
                            LEFT JOIN dbo.HRDATA H ON ( H.DISPLAY_NAME = REPLACE(A.姓名,
                                                              ' ', '')
                                                         AND A.单位 LIKE '%雇员%'
                                                       )
        ORDER BY PROVIDE_DATE ASC

--清理临时表数据
        TRUNCATE TABLE ##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A    
        DROP TABLE ##TEMP_临工雇员_D2D119452A60497099FB24DF477EEB0A
    END