EXCEL的数据倒入到数据库-CSDN博客

/*
EXEC E_ExcelToDatabase 'c:\题库.xls','[exam$]','where nid<>null','*'
CreateTime:2007-11-30 09:00
Author:wenjl
explain:将EXCEL的数据倒入到数据库
*/

CREATE PROC E_ExcelToDatabase
@Path NVarChar(200),
@TableName NVarChar(100),--主表名称
@ChildTableName NVarChar(100),--子表名称
@Condition NVarChar(300),--主表条件
@ChildCondition NVarChar(300),--子表条件
@GroupId NVarChar(32)--题库ID
AS

IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'[TEMPDB]..[##TMP]')) DROP TABLE ##TMP
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'[TEMPDB]..[##TMP1]')) DROP TABLE ##TMP1
--获得EXCEL数据
DECLARE @Link NVarChar(400)
DECLARE @Data NVarChar(300)
DECLARE @strsql NVarChar(2000)
DECLARE @strsql2 NVarChar(2000)
--连接EXCEL
SET @Link='MicroSoft.Jet.OleDB.4.0'
SET @Data='Excel 8.0;HDR=yes;Database='+@Path+''
IF @TableName='[exam$]'
BEGIN
SET @strsql = '
SELECT
   *
INTO ##TMP
FROM OpenDataSource('''+ @Link + ''', '''+ @Data + ''')...'+@TableName+''+@Condition+''
EXEC(@strsql)
UPDATE ##TMP
   SET ID=REPLACE(NEWID(),'-',''),
       GROUP_ID = @GroupId

SET @strsql2 = '
SELECT
*
INTO ##TMP1
FROM OpenDataSource('''+ @Link + ''', '''+ @Data + ''')...'+@ChildTableName+''+@ChildCondition+''

EXEC(@strsql2)
Alter Table ##TMP1 Alter Column EXAM_ID NVARCHAR(32)

UPDATE ##TMP1
SET EXAM_ID = ##TMP.ID
FROM ##TMP
LEFT JOIN ##TMP1
ON ##TMP.NID= ##TMP1.EXAM_ID

INSERT INTO EXAM
        SELECT ID,
               TYPE_FLAG = TYPE_FLAG,
               SNO = SNO,
               KIND_ID = NULL,
               BASE_ID = NULL,
               QUESTION = QUESTION,
               ANSWER = RTRIM(LTRIM(ANSWER)),
               GROUP_ID = GROUP_ID,
               CLASS_ID = CLASS_ID,
               POST =(SELECT DBO.FUN_GET_POST_ID(POST))
        FROM ##TMP

        INSERT INTO EXAM_ITEM
        SELECT EXAM_ID =EXAM_ID ,
               CHOICE = RTRIM(LTRIM(CHOICE)),
               NTEXT = NTEXT
        FROM ##TMP1
END
GO

版权说明

  如果标题未标有<转载、转>等字则属于作者原创，欢迎转载，其版权归作者和博客园共有。
  作      者：温景良
  文章出处：http://wenjl520.cnblogs.com/ 或 http://www.cnblogs.com/

分类: SQL Server

好文要顶关注我收藏该文