/*

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