/*
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>: