/*
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 = '
*
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
版权说明
如果标题未标有<转载、转>等字则属于作者原创，欢迎转载，其版权归作者和博客园共有。
作 者：温景良
<a href="http://home.cnblogs.com/u/wenjl520/">温景良(Jason)</a>
<a href="http://home.cnblogs.com/u/wenjl520/followees">关注 - 32</a>
<a href="http://home.cnblogs.com/u/wenjl520/followers">粉丝 - 161</a>
<a>+加关注</a>
<a></a>
<a href="http://www.ucancode.com/index.htm" target="_blank">【推荐】超50万VC++源码: 大型工控、组态\仿真、建模CAD源码2018！</a>
<a href="https://dnspod.cloud.tencent.com/act/yearendsales?from=IT&fromSource=gwzcw.741110.741110.741110" target="_blank">【推荐】腾讯云新用户域名抢购1元起，抓紧抢购</a>
<b>最新IT新闻</b>:
<b>最新知识库文章</b>: