jsp:
<input type="file" id="file" name="file" style="width: 450px;" >
点击“Import”进入process方法:(Action开始 )
将文件上传到服务器:
DynaActionForm tariffForm = (DynaActionForm) form; FormFile tempFile = (FormFile) tariffForm.get("file"); //String myFile = (String)tariffForm.get("myfile"); log.info("excel fileName : " + tempFile.getFileName()); //将tempFile path保存到cookies中去 String uploadTariffFile = tempFile.getFileName(); Cookie uploadFilePath = new Cookie("uploadFilePath", uploadTariffFile); response.addCookie(uploadFilePath); String actionto = "import"; if(!XslUtil.isXslType(tempFile.getFileName())){ request.setAttribute(MyConstants.ERROR_KEY, "The file must be xsl format.eg .(xsl)"); return "importFail"; } //检XslFile if(!checkXslFile(request, tempFile)){ request.setAttribute(MyConstants.ERROR_KEY, "The file must be xsl format.eg .(xsl)"); return "importFail"; } List list = new ArrayList(); File cFile =null; String xslPath = null; try { //1.将excel文件上传到tariff中去先 Configuration config = (Configuration) request.getSession().getServletContext().getAttribute(MyConstants.CONFIGURATION_KEY); xslPath = config.getString("ImportHotelPath"); log.info("Excel file: " + xslPath); cFile = new File(xslPath,tempFile.getFileName()); if(cFile.exists()){ cFile.delete(); } this.writeFile(tempFile, cFile); } catch (Exception e) { log.error("upload excel file error:" + e.getMessage()); request.setAttribute(MyConstants.ERROR_KEY, "Upload excel file error! "); return "importFail"; }
辅助方法:
public static boolean isXslType(String fileName){ Pattern p = Pattern.compile(REG_XSL_TYPE,Pattern.CASE_INSENSITIVE); Matcher m = p.matcher(fileName); return m.find(); }
private boolean checkXslFile(HttpServletRequest request,FormFile tempFile) throws SystemException { if (tempFile == null) { log.error(" error :file is null."); request.setAttribute(MyConstants.ERROR_KEY, "The file isn't exist."); return false; } if (tempFile.getFileSize() > 1024 * 1024 * 45) { log.error(" error :file lenght is error."); request.setAttribute(MyConstants.ERROR_KEY, "The size of file cann't more than 45M."); return false; } return true; }
public void writeFile(FormFile formFile, File file) throws Exception{ byte [] fileDate=formFile.getFileData(); FileOutputStream fileOutStream = null; try { fileOutStream = new FileOutputStream(file); fileOutStream.write(fileDate); fileOutStream.flush(); } finally { if(fileOutStream != null) { fileOutStream.close(); fileOutStream = null; } } }
调用存储过程ImportHotel:
boolean result = tariffImportFacade.importHotel(cFile.getPath());
@Transactional(propagation=Propagation.REQUIRED) public boolean importHotel(final String path) throws SystemException { boolean ff = (Boolean) this.baseDao.getHibernateTemplate().execute( new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Connection conn = session.connection(); java.sql.CallableStatement cs = conn.prepareCall("{call FareAdmin.ImportHotel(?)}", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY); cs.setString(1, path); boolean bb = cs.execute(); if(!bb){ throw new SQLException("call FareAdmin.ImportHotel(?) error occured."); } return bb; } }); if(!ff){ throw new SystemException("call FareAdmin.ImportHotel(?) error occured."); } log.info(" read ok."); return true; }
从临时表中查出数据并验证:
String hql = "select * from FareAdmin.temp_hotel"; list = tariffImportFacade.findListByHQL(hql); String s = this.checktariff(list, request); if(!s.equalsIgnoreCase("")) { request.setAttribute(MyConstants.ERROR_KEY, s); return "importFail"; }
辅助方法:
@Transactional(propagation=Propagation.REQUIRED) public List findListByHQL(final String hql) throws SystemException { List list = new ArrayList(); list = (List) this.baseDao.getHibernateTemplate().execute( new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { SQLQuery sql = session.createSQLQuery(hql); List list = sql.list(); return list; } }); return list; }
保存数据到数据库部分:
String error = ""; try { error = this.saveTariffListToDB(list, request); } catch (Exception e) { log.error(e.getMessage()); e.printStackTrace(); request.setAttribute(MyConstants.ERROR_KEY, "Can't save data to databases!"); return "importFail"; } if(!"".equals(error)) { request.setAttribute(MyConstants.ERROR_KEY, error); } else { request.setAttribute(MyConstants.MESSAGE_KEY, "Import tariff data success!"); } return "import";
Action到此结束!
private String saveTariffListToDB(List list,HttpServletRequest request) throws Exception{ HttpSession session = request.getSession(); Agentuser user = (Agentuser)session.getAttribute(MyConstants.CLIENTUSER_KEY); List<Tariff> tlist = new ArrayList<Tariff>(); List<com.techson.himsnanhwa.admin.hibernate.hbm.Tariff> updatelist = (List<com.techson.himsnanhwa.admin.hibernate.hbm.Tariff>)request.getAttribute("tariffUpdateList"); if(list.size()<=4){ return "The excel can't find the record!"; }else{ for(int i = 4;i < list.size();i ++){ log.info("conver to tariff no = " + (i-3)); Object[] objTemp = (Object[]) list.get(i); for (int j = 0; j < objTemp.length; j++) { if(null==objTemp[j]){ objTemp[j] = ""; } } Tariff tariff = new Tariff(); tariff.setUpdateby(user.getLoginname()); ... tlist.add(tariff); //将临时表中查出的数据保存到tariff表中(将临时表中属性F1、F2对应到数据库hotel-ng-egl-temp的表tariff中) } try { String name = user.getLoginname(); String message = tariffImportFacade.saveList(tlist,name); return message; } catch (Exception e) { log.error(e.getMessage()); e.printStackTrace(); return "Can't save data to databases!"; } } }
@Transactional(propagation=Propagation.REQUIRED) public String saveList(List<Tariff> entity,final String loginName) throws SystemException{ int index = 0; boolean isDelete = (Boolean) this.baseDao.getHibernateTemplate() .execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Boolean ret = true; try { SQLQuery query1 = session.createSQLQuery("delete from FareAdmin.cxlpolicy"); SQLQuery query2 = session.createSQLQuery("delete from FareAdmin.focpolicy"); SQLQuery query3 = session.createSQLQuery("delete from FareAdmin.blackout"); SQLQuery query4 = session.createSQLQuery("delete from FareAdmin.tariff"); query1.executeUpdate(); query2.executeUpdate(); query3.executeUpdate(); query4.executeUpdate(); } catch (Exception e) { log.error(e); ret = false; } return ret; } }); try { for(int i = 0; i < entity.size();i ++){ index = i; Tariff tariff = (Tariff)entity.get(i); System.out.println("Insert record "+String.valueOf(i)+" Tariff "+String.valueOf(tariff.getHotelid())+" "+String.valueOf(tariff.getRoomcategoryid())); System.out.println(tariff.getValidfrom()); this.getBaseDao().insert(tariff); Iterator<Focpolicy> it = tariff.getFocpolicies().iterator(); while(it.hasNext()){ Focpolicy focpolicy = it.next(); if(null != focpolicy){ focpolicy.setTariff(tariff); this.getBaseDao().getHibernateTemplate().save(focpolicy); } } Iterator<Cxlpolicy> itc = tariff.getCxlpolicies().iterator(); while(itc.hasNext()){ Cxlpolicy cxlpolicy = itc.next(); if(null != cxlpolicy){ cxlpolicy.setTariff(tariff); this.getBaseDao().getHibernateTemplate().save(cxlpolicy); } } Iterator<Blackout> bkSet = tariff.getBlackout().iterator(); while(bkSet.hasNext()) { Blackout bk = bkSet.next(); if(null != bk) { bk.setTariff(tariff); this.getBaseDao().getHibernateTemplate().save(bk); } } if(i % 2000 == 0){ this.getBaseDao().getHibernateTemplate().flush(); this.getBaseDao().getHibernateTemplate().clear(); } } } catch (RuntimeException e) { this.errorMessage = "Excel duplicate row in " + (index); throw new SystemException("Excel duplicate row in " + (index), "Duplicate key"); } String message = (String)this.baseDao.getHibernateTemplate().execute( new HibernateCallback(){ public Object doInHibernate(Session session) throws HibernateException, SQLException{ Connection conn = session.connection(); java.sql.CallableStatement cs = conn.prepareCall("{Call FareAdmin.ImportAndUpdateTariff(?)}", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY); cs.setString(1, loginName); cs.execute(); String message = ""; ResultSet rs = cs.getResultSet(); if(rs != null) { while(rs.next()) { String msg = rs.getString(1); int linenumber = rs.getInt(2); message += "<br/> Row : " + linenumber + " " + msg + ""; } } return message; } }); log.info(" save ok."); return message; }
导出Excel,将Excel中数据导入到表中:
导入时,先将文件上传到服务器,再调用hotel-ng-egl-temp数据库中存储过程ImportHotel ,存储过程创建临时表temp-hotel(属性为F1、F2...),并将数据导入到表temp-hotel中 ,然后程序从temp-hotel表中查出数据,将其中数据导入自定义的 hotel-ng-egl-temp数据库中 表tariff ,再调用存储过程ImportAndUpdateTariff,从tariff中查出数据导入数据库hotel-ng-egl的表Tariff 中。
存储过程:
ImportHotel:(创建一个临时表,并将Excel中数据赋给它)
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure
/****** Object: Stored Procedure [ImportHotel] Script Date: 2011/3/30 15:58:48 ******/ USE [hotel_nh_egl_temp]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE [ImportHotel] (@xlsFile varchar(255)) //该存储过程参数 WITH EXECUTE AS CALLER AS SELECT 'SUCCESS' AS boolsuccess begin EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE declare @sql varchar(5000) declare @errMessage varchar(5000) if object_id('FareAdmin.temp_hotel') is not null DROP TABLE FareAdmin.temp_hotel print(@xlsFile) set @sql = 'SELECT * INTO FareAdmin.temp_hotel FROM openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;HDR=NO;IMEX=1; Database='+@xlsFile+ ''', ''select * from [Sheet1$]'')' exec(@sql) end GO
知识点:
1、object_id('objectname');
返回数据库对象标识号。
参数表示要使用的对象,返回类型为int,表示该对象在系统中的编号。
if object_id('FareAdmin.temp_hotel') is not null
DROP TABLE FareAdmin.temp_hotel
2、
你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据,或者批量向SQL Server表导入数据 。在这种情况下,你可以在T-SQL(Transact-SQL,微软对SQL的扩展)中用OPENROWSET函数给数据源传入一个连接串和查询来提取需要的数据。
你可以使用OPENROWSET函数从任何支持注册OLE DB的数据源获取数据,比如从SQL Server或Access的远程实例中提取数据。如果你用OPENROWSET从SQL Server实例中获取数据,该实例必须配置为允许ad hoc分布式查询 。
要配置远程SQL Server实例支持ad hoc查询,需要使用系统存储过程sp_configure先设置advanced options,再启用Ad Hoc Distributed Queries(ad hoc分布式查询)。请看下面的T-SQL脚本:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE;
GO
要注意的是,在运行完存储过程之后,你必须运行“RECONFIGURE”命令。
一旦你配置好了远程SQL Server实例,你就可以对它使用OPENROWSET函数。这个函数可以在SELECT语句的FROM从句里使用。下面的例子显示了该函数的基本语法:
OPENROWSET('provider', 'connection string', target)
Provider —— 某特定数据源支持的OLE DB提供者的人机友好名称(ProgID)。Provider的名字必须用单引号括起来。
Connection string —— 连接串。它是与具体提供者provider相关的字符串,包括连接到给字符串中指定的数据源所需要的细节信息。根据provider的不同,连接串信息需要用一对或多对单引号括起来。
Target —— target参数可以使一个数据库对象或者一个查询。
set @sql = 'SELECT * INTO FareAdmin.temp_hotel FROM openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;HDR=NO;IMEX=1; Database='+@xlsFile+ ''', ''select * from [Sheet1$]'')'
exec(@sql)
3、select * into target_table from source_table;
//此句要求目标表不存在,在插入时候自动创建
//并将source_table表中所有数据复制到target_table表中(两表结构一样)
4、WITH EXECUTE AS CALLER
Mary 创建了一个引用某个表的存储过程,她不拥有该表,但具有对该表的 SELECT 权限。她在 CREATE PROCEDURE 语句中指定了 EXECUTE AS CALLER,如下例中所示:
CREATE PROCEDURE AccessTable
WITH EXECUTE AS CALLER
AS SELECT * FROM dbo.SomeTable;
然后,Mary 将授予 Scott 对存储过程的 EXECUTE 权限。
当 Scott 执行存储过程时,数据库引擎将验证他(调用方)是否具有执行该存储过程的权限。Scott 拥有 EXECUTE 权限,但是由于 Mary 不是被引用表的所有者,因此数据库引擎将检查 Scott 是否对该表具有权限。如果 Scott 没有权限,则该存储过程语句将失败。
ImportAndUpdateTariff:
/****** Object: Stored Procedure [ImportAndUpdateTariff] Script Date: 2011/3/30 15:59:32 ******/ USE [hotel_nh_egl_temp]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- ============================================= -- Author: KO -- Create date: 17NOV2010 -- Description: Import tariff and update tariff from temp table -- ============================================= CREATE PROCEDURE [ImportAndUpdateTariff] (@updatedby nvarchar(50)) WITH EXECUTE AS CALLER AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- DECLARE @TranName char(2) -- SET @TranName = 'T1' -- PRINT @TranName DECLARE @itfilterfetch int DECLARE @vittariffid numeric(18, 0), @vithotelid numeric(18, 0), @vitclienttype int, @vitratetype int, @vitroomcategoryid numeric(18, 0), @vitrefhotelid numeric(18, 0), @vitvalidfrom datetime, @vitvalidto datetime, @vitdayofweek varchar(13), @vitmealcode char(2), @vitsuppliercode varchar(200), @vitapplnationality varchar(600), @vtariffid numeric(18,0) DECLARE --@vhotelid, --@vsuppliercode, @vbookingcode varchar(200), --@vclienttype, --@vratetype, --@vroomcategoryid, --@vrefhotelid, --@vvalidfrom, --@vvalidto, @vsgl decimal(18,2), @vtwn decimal(18,2), @vdbl decimal(18,2), @vtrp decimal(18,2), @vqud decimal(18,2), @vxba decimal(18,2), @vxbc decimal(18,2), @vtsu decimal(18,2), @vdsu decimal(18,2), @vsdbl decimal(18,2), @vetwn decimal(18,2), --@vmealcode, @vmeal decimal(18,2), @vbonusmeal decimal(18,2), @vaaltermealcode char(2), @vaaltermeal decimal(18,2), @vbaltermealcode char(2), @vbaltermeal decimal(18,2), @vcostcurr char(3), --@v[dayofweek], @vminstay int, @vmaxstay int, --@vapplnationality, @vnonapplnationality varchar(600), @vbonusstay varchar(50), @vmaxbonusstay decimal(18,1), @vinclmealonbonusstay bit, @vchargemealonbonusstay bit, @vallowextensionnight bit, @vallow2ndnightrate bit, --allowprestayrate --specialnight --maxextnight @vratedesc_en varchar(4000), @vratedesc_tw nvarchar(4000), @vratedesc_cn nvarchar(4000), @vcplycharge decimal(18,2), @vcplyitem_en varchar(200), @vcplyitem_tw nvarchar(200), @vcplyitem_cn nvarchar(200), @vsuppcharge decimal(18,2), @vsupphalfboard decimal(18,2), @vsuppfullboard decimal(18,2), @vsuppmisc decimal(18,2), @vsuppitem_en varchar(200), @vsuppitem_tw nvarchar(200), @vsuppitem_cn nvarchar(200), -- hasfocpolicy -- hascxlpolicy @vcxldesc_en varchar(4000), @vcxldesc_tw nvarchar(4000), @vcxldesc_cn nvarchar(4000), @vremarks nvarchar(4000), @vinternalremarks nvarchar(4000), @vagentpermit varchar(4000), @vrsvntel nvarchar(50), @vrsvnfax nvarchar(50), @vrsvnemail nvarchar(50), @vhasfocpolicy bit, @vhascxlpolicy bit, @vadditionalitemtype1 varchar(4), @vadditionalitemtype2 varchar(4), @vadditionalitemtype3 varchar(4), @vadditionalitemtype4 varchar(4), @vadditionalitemtype5 varchar(4), @vadditionalitemtype6 varchar(4), @vadditionalitemname1 nvarchar(50), @vadditionalitemname2 nvarchar(50), @vadditionalitemname3 nvarchar(50), @vadditionalitemname4 nvarchar(50), @vadditionalitemname5 nvarchar(50), @vadditionalitemname6 nvarchar(50), @vadditionalitemprice1 decimal(18,2), @vadditionalitemprice2 decimal(18,2), @vadditionalitemprice3 decimal(18,2), @vadditionalitemprice4 decimal(18,2), @vadditionalitemprice5 decimal(18,2), @vadditionalitemprice6 decimal(18,2), @linenumber int --transportation --cplymarkup --cplybreakfastmarkup --agentgroup --创建一个临时表 DECLARE @resultTbl TABLE ( duplicateMsg varchar(400) NOT NULL, linenumber int ) -- 创建游标 DECLARE IT_FILTER_CURSOR CURSOR DYNAMIC FOR SELECT internalid, tariffid,hotelid, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, [dayofweek], mealcode, suppliercode, applnationality, bookingcode,sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, minstay, maxstay, nonapplnationality, bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw, ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn, cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail, hasfocpolicy, hascxlpolicy, additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6, additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6, additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6,linenumber FROM [FareAdmin].[tariff] -- 打开游标 OPEN IT_FILTER_CURSOR -- 读取游标 FETCH FIRST FROM IT_FILTER_CURSOR INTO @vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality, @vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu,@vsdbl,@vetwn, @vmeal,@vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay, @vmaxstay, @vnonapplnationality, @vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay, @vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw, @vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw, @vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard, @vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn, @vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks, @vinternalremarks, @vagentpermit, @vrsvntel, @vrsvnfax, @vrsvnemail, @vhasfocpolicy, @vhascxlpolicy, @vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4, @vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2, @vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6, @vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4, @vadditionalitemprice5,@vadditionalitemprice6,@linenumber SET @itfilterfetch = @@FETCH_STATUS -- print 'itfilterfetch : ' + @itfilterfetch DECLARE @vRecHit INT DECLARE @duplicate INT DECLARE @index INT DECLARE @tmpDuplicateMsg nvarchar(500) set @tmpDuplicateMsg = '' while (@itfilterfetch = 0) begin set @index = 0 -- 检查hotel_nh_egl tariff table中是否duplicate SELECT @vRecHit=count(*) FROM [hotel_nh_egl].[FareAdmin].[tariff] t WHERE t.hotelid=@vithotelid AND t.clienttype=@vitclienttype AND t.ratetype=@vitratetype AND t.roomcategoryid=@vitroomcategoryid AND t.mealcode=@vitmealcode AND t.suppliercode=@vitsuppliercode AND t.applnationality=@vitapplnationality AND (t.refhotelid = @vitrefhotelid OR (ISNULL(@vitrefhotelid,0)=0 AND t.refhotelid is Null)) AND (t.validfrom <= @vitvalidfrom OR t.validfrom <= @vitvalidto) AND (t.validto >= @vitvalidto OR t.validto >= @vitvalidfrom) AND ( (CHARINDEX('1',t.dayofweek)>0 AND CHARINDEX('1',@vitdayofweek)>0) OR (CHARINDEX('2',t.dayofweek)>0 AND CHARINDEX('2',@vitdayofweek)>0) OR (CHARINDEX('3',t.dayofweek)>0 AND CHARINDEX('3',@vitdayofweek)>0) OR (CHARINDEX('4',t.dayofweek)>0 AND CHARINDEX('4',@vitdayofweek)>0) OR (CHARINDEX('5',t.dayofweek)>0 AND CHARINDEX('5',@vitdayofweek)>0) OR (CHARINDEX('6',t.dayofweek)>0 AND CHARINDEX('6',@vitdayofweek)>0) OR (CHARINDEX('7',t.dayofweek)>0 AND CHARINDEX('7',@vitdayofweek)>0) OR (CHARINDEX('0',t.dayofweek)>0 AND CHARINDEX('0',@vitdayofweek)>0) ) IF @vRecHit = 0 PRINT 'NO Duplicate' IF @vRecHit = 1 and @vtariffid is null begin PRINT 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+@vitsuppliercode + ' mealcode='+@vitmealcode SET @tmpDuplicateMsg = 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+@vitsuppliercode + ' mealcode='+@vitmealcode PRINT @tmpDuplicateMsg INSERT INTO @resultTbl ( duplicateMsg,linenumber ) values ( @tmpDuplicateMsg,@linenumber ) SET @duplicate = 1 PRINT @duplicate end -- 检查tariff table中是否duplicate SELECT @vRecHit=count(*) FROM [FareAdmin].[tariff] t WHERE t.hotelid=@vithotelid AND t.clienttype=@vitclienttype AND t.ratetype=@vitratetype AND t.roomcategoryid=@vitroomcategoryid AND t.mealcode=@vitmealcode AND t.suppliercode=@vitsuppliercode AND t.applnationality=@vitapplnationality AND (t.refhotelid = @vitrefhotelid OR (ISNULL(@vitrefhotelid,0)=0 AND t.refhotelid is Null)) AND (t.validfrom <= @vitvalidfrom OR t.validfrom <= @vitvalidto) AND (t.validto >= @vitvalidto OR t.validto >= @vitvalidfrom) AND ( (CHARINDEX('1',t.dayofweek)>0 AND CHARINDEX('1',@vitdayofweek)>0) OR (CHARINDEX('2',t.dayofweek)>0 AND CHARINDEX('2',@vitdayofweek)>0) OR (CHARINDEX('3',t.dayofweek)>0 AND CHARINDEX('3',@vitdayofweek)>0) OR (CHARINDEX('4',t.dayofweek)>0 AND CHARINDEX('4',@vitdayofweek)>0) OR (CHARINDEX('5',t.dayofweek)>0 AND CHARINDEX('5',@vitdayofweek)>0) OR (CHARINDEX('6',t.dayofweek)>0 AND CHARINDEX('6',@vitdayofweek)>0) OR (CHARINDEX('7',t.dayofweek)>0 AND CHARINDEX('7',@vitdayofweek)>0) OR (CHARINDEX('0',t.dayofweek)>0 AND CHARINDEX('0',@vitdayofweek)>0) ) AND t.internalid <> @vittariffid IF @vRecHit = 0 PRINT 'NO Duplicate' IF @vRecHit = 1 begin PRINT 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+ @vitsuppliercode + ' mealcode='+@vitmealcode SET @tmpDuplicateMsg = 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+@vitsuppliercode + ' mealcode='+@vitmealcode PRINT @tmpDuplicateMsg INSERT INTO @resultTbl ( duplicateMsg,linenumber ) values ( @tmpDuplicateMsg,@linenumber ) SET @duplicate = 1 PRINT @duplicate end -- set @index = @index + 1 FETCH NEXT FROM IT_FILTER_CURSOR INTO @vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality, @vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu, @vsdbl,@vetwn,@vmeal, @vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay, @vmaxstay, @vnonapplnationality,@vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay, @vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw, @vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw, @vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard, @vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn, @vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks, @vinternalremarks, @vagentpermit, @vrsvntel, @vrsvnfax, @vrsvnemail, @vhasfocpolicy, @vhascxlpolicy, @vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4, @vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2, @vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6, @vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4, @vadditionalitemprice5,@vadditionalitemprice6,@linenumber SET @itfilterfetch = @@FETCH_STATUS end -- 关闭游标 CLOSE IT_FILTER_CURSOR -- 删除游标 DEALLOCATE IT_FILTER_CURSOR -- 如果存在duplicate return IF @duplicate = 1 begin PRINT @tmpDuplicateMsg --SELECT @duplicateMsg = @tmpDuplicateMsg --SELECT @status = 0 SELECT duplicateMsg,linenumber from @resultTbl DELETE from @resultTbl return end -- 如果不存在duplicate --BEGIN TRY BEGIN TRANSACTION print 'BEGIN TRANSACTION' -- 创建游标 DECLARE RESULT_CURSOR CURSOR DYNAMIC FOR SELECT internalid, tariffid,hotelid, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, [dayofweek], mealcode, suppliercode, applnationality, bookingcode,sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, minstay, maxstay,nonapplnationality, bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw, ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn, cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail, hasfocpolicy, hascxlpolicy, additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6, additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6, additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6 FROM [FareAdmin].[tariff] -- 打开游标 OPEN RESULT_CURSOR -- 读取游标 FETCH FIRST FROM RESULT_CURSOR INTO @vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality, @vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu,@vsdbl,@vetwn, @vmeal,@vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay, @vmaxstay, @vnonapplnationality, @vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay, @vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw, @vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw, @vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard, @vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn, @vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks, @vinternalremarks, @vagentpermit, @vrsvntel, @vrsvnfax, @vrsvnemail, @vhasfocpolicy, @vhascxlpolicy, @vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4, @vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2, @vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6, @vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4, @vadditionalitemprice5,@vadditionalitemprice6 SET @itfilterfetch = @@FETCH_STATUS WHILE @itfilterfetch = 0 BEGIN IF @vtariffid is null begin PRINT 'INSERT tariff FROM temp id is'+STR(@vittariffid) INSERT INTO [hotel_nh_egl].[FareAdmin].[tariff] ( hotelid, suppliercode, bookingcode, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn, mealcode, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, [dayofweek], minstay, maxstay, applnationality, nonapplnationality, bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw, ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn, cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail, hasfocpolicy, hascxlpolicy, updateby, createby, additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6, additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6, additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6 ) SELECT hotelid, suppliercode, bookingcode, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn, mealcode, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, [dayofweek], minstay, maxstay, applnationality, nonapplnationality, bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw, ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn, cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail, hasfocpolicy, hascxlpolicy, @updatedby, @updatedby, additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6, additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6, additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6 FROM [FareAdmin].[tariff] WHERE internalid = @vittariffid DECLARE @itNewTariffId numeric(18,0) SELECT @itNewTariffId = @@IDENTITY PRINT 'NEW TARIFF ID: '+str(@itNewTariffId) INSERT INTO [hotel_nh_egl].[FareAdmin].[cxlpolicy] (tariffid, cxldays, cxlnights, cxlpercent) SELECT @itNewTariffId, cxldays, cxlnights, cxlpercent FROM [FareAdmin].[cxlpolicy] WHERE tariffid = @vittariffid INSERT INTO [hotel_nh_egl].[FareAdmin].[focpolicy] (tariffid, foc_noofnight, foc_en, foc_tw, foc_cn) SELECT @itNewTariffId, foc_noofnight, foc_en, foc_tw, foc_cn FROM [FareAdmin].[focpolicy] WHERE tariffid = @vittariffid INSERT INTO [hotel_nh_egl].[FareAdmin].[blackout] (tariffid, validfrom, validto) SELECT @itNewTariffId, validfrom, validto FROM [FareAdmin].[blackout] WHERE tariffid = @vittariffid end else begin print @vtariffid UPDATE [hotel_nh_egl].[FareAdmin].[tariff] SET [hotelid] = @vithotelid ,[suppliercode] = @vitsuppliercode ,[bookingcode] = @vbookingcode ,[clienttype] = @vitclienttype ,[ratetype] = @vitratetype ,[roomcategoryid] = @vitroomcategoryid ,[refhotelid] = @vitrefhotelid ,[validfrom] = @vitvalidfrom ,[validto] = @vitvalidto ,[sgl] = @vsgl ,[twn] = @vtwn ,[dbl] = @vdbl ,[trp] = @vtrp ,[qud] = @vqud ,[xba] = @vxba ,[xbc] = @vxbc ,[tsu] = @vtsu ,[dsu] = @vdsu ,[sdbl]= @vsdbl ,[etwn]= @vetwn ,[mealcode] = @vitmealcode ,[meal] = @vmeal ,[bonusmeal] = @vbonusmeal ,[aaltermealcode] = @vaaltermealcode ,[aaltermeal] = @vaaltermeal ,[baltermealcode] = @vbaltermealcode ,[baltermeal] = @vbaltermeal ,[costcurr] = @vcostcurr ,[dayofweek] = @vitdayofweek ,[minstay] = @vminstay ,[maxstay] = @vmaxstay ,[applnationality] = @vitapplnationality ,[nonapplnationality] = @vnonapplnationality ,[bonusstay] = @vbonusstay ,[maxbonusstay] = @vmaxbonusstay ,[inclmealonbonusstay] = @vinclmealonbonusstay ,[chargemealonbonusstay] = @vchargemealonbonusstay ,[allowextensionnight] = @vallowextensionnight ,[allow2ndnightrate] = @vallow2ndnightrate --,[allowprestayrate] = <allowprestayrate, bit,> --,[specialnight] = <specialnight, int,> --,[maxextnight] = <maxextnight, int,> ,[ratedesc_en] = @vratedesc_en ,[ratedesc_tw] = @vratedesc_tw ,[ratedesc_cn] = @vratedesc_cn ,[cplycharge] = @vcplycharge ,[cplyitem_en] = @vcplyitem_en ,[cplyitem_tw] = @vcplyitem_tw ,[cplyitem_cn] = @vcplyitem_cn ,[suppcharge] = @vsuppcharge ,[supphalfboard] = @vsupphalfboard ,[suppfullboard] = @vsuppfullboard ,[suppmisc] = @vsuppmisc ,[suppitem_en] = @vsuppitem_en ,[suppitem_tw] = @vsuppitem_tw ,[suppitem_cn] = @vsuppitem_cn ,[hasfocpolicy] = @vhasfocpolicy ,[hascxlpolicy] = @vhascxlpolicy ,[cxldesc_en] = @vcxldesc_en ,[cxldesc_tw] = @vcxldesc_tw ,[cxldesc_cn] = @vcxldesc_cn ,[remarks] = @vremarks ,[internalremarks] = @vinternalremarks ,[agentpermit] = @vagentpermit ,[rsvntel] = @vrsvntel ,[rsvnfax] = @vrsvnfax ,[rsvnemail] = @vrsvnemail --,[updatedate] = <updatedate, datetime,> ,[updateby] = @updatedby --,[createdate] = <createdate, datetime,> --,[createby] = <createby, nvarchar(50),> ,[additionalitemtype1] = @vadditionalitemtype1 ,[additionalitemtype2] = @vadditionalitemtype2 ,[additionalitemtype3] = @vadditionalitemtype3 ,[additionalitemtype4] = @vadditionalitemtype4 ,[additionalitemtype5] = @vadditionalitemtype5 ,[additionalitemtype6] = @vadditionalitemtype6 ,[additionalitemname1] = @vadditionalitemname1 ,[additionalitemname2] = @vadditionalitemname2 ,[additionalitemname3] = @vadditionalitemname3 ,[additionalitemname4] = @vadditionalitemname4 ,[additionalitemname5] = @vadditionalitemname5 ,[additionalitemname6] = @vadditionalitemname6 ,[additionalitemprice1] = @vadditionalitemprice1 ,[additionalitemprice2] = @vadditionalitemprice2 ,[additionalitemprice3] = @vadditionalitemprice3 ,[additionalitemprice4] = @vadditionalitemprice4 ,[additionalitemprice5] = @vadditionalitemprice5 ,[additionalitemprice6] = @vadditionalitemprice6 --,[transportation] = <transportation, bit,> --,[cplymarkup] = <cplymarkup, int,> --,[cplybreakfastmarkup] = <cplybreakfastmarkup, decimal(18,2),> --,[agentgroup] = <agentgroup, numeric,> WHERE internalid = @vtariffid end FETCH NEXT FROM RESULT_CURSOR INTO @vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality, @vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu, @vsdbl,@vetwn,@vmeal, @vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay, @vmaxstay,@vnonapplnationality, @vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay, @vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw, @vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw, @vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard, @vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn, @vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks, @vinternalremarks, @vagentpermit, @vrsvntel, @vrsvnfax, @vrsvnemail, @vhasfocpolicy, @vhascxlpolicy, @vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4, @vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2, @vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6, @vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4, @vadditionalitemprice5,@vadditionalitemprice6 SET @itfilterfetch = @@FETCH_STATUS END -- 关闭游标 CLOSE RESULT_CURSOR -- 删除游标 DEALLOCATE RESULT_CURSOR --SELECT @duplicateMsg = @tmpDuplicateMsg --SELECT 'SUCCESS' AS boolsuccess SELECT duplicateMsg,linenumber from @resultTbl --SELECT @status = 1 COMMIT TRANSACTION END GO
。。。
相关推荐
B/S架构开发的系统在进行数据的导入存储的批处理的过程中如何将excel数据导入到数据库中并保存 这个文档文提供了一种思路 和部分的代码 仅供参考
数据导入到excel和读取excel数据到数据库 数据导入到excel和读取excel数据到数据库
Java实现从excel中批量高效导入数据到数据库中,包括图片的导入存储,读取图片显示在jsp页面等,需要下载JspSmartUpload.jar和jxl.jar两个jar包,将两个包导入项目才能运行
将Excel数据导入到SQLServer中,可以选择要导入的Excel文件和Sheet名称,表名。通过自动编写存储过程来实现导入功能。实际使用无须这么麻烦,可以作为数据导入和存储过程参考例子。
遍历指定文件夹下的excel文件,打开excel文件之后读取多个sheet中指定的sheet数据,并把读取的数据导入到mysql数据库中,是python写的源码,环境是python3。
相关博客:https://blog.csdn.net/m0_51197424/article/details/124454553 开发技术:SpringBoot MyBatis MySQL 实现内容:开发接口实现导入Excel数据后,将数据存储到数据库中
以Excel表格形式导入到数据库,为此写了一个这样一个测试程序,当然,里面导入的Excel表格的字段、表名都可以自定义的,过程比较简单,主要用到了Sql Server和Excel的接口类库
读取excel表,导入到数据库中然后更新正式库 把DBhelper里的链接修改了。。 建立好test 和test_1注释掉那个2个存储过称就就应该能用了 CREATE TABLE [dbo].[test]( [日期] [datetime] NULL, [车号] [nvarchar]...
用vs2013和office2013写的,用于导入Excel表格里的数据库中,数据库是2013的内嵌数据库,导入中文会乱码,有sql2012的转一下数据库编码就可以了(理论上,因为我没2012的sql)。仅供学习!
此为word文档,导入方法。这个方法是到目前为止我见到最好的方法
用java实现把excel数据导入到mysql数据库中.pdf
配置规则的时候,需要配置excel要导入的表,字段等信息,导入的时候依据这个规则将数据导入到数据库中。这样一来,在进行excel导入时候只需要为某种数据配置一个规则,然后调用封装的导入类就可以完成整个excel导入...
实现打开Excel表路径,然后再MFC中将Excel表中数据取出存储到Access数据库中
上传导入excel文件中的数据到数据库中 前端导出页面:export.html 访问地址:http://localhost:8082/toExport 文件导出下载 下载导出数据库中的数据到excel文件中 我小白一枚,写这个东东也踩了很多坑,在...
实现EXCEL文件上传,并将其中数据导入数据库中
控制下命令行实现sqlite数据库导入导出EXCEL文件格式 ,希望对你有所帮助
MySQL是一种开源的关系型数据库管理系统(RDBMS),广泛用于Web应用程序和其他各种软件开发项目中。它提供了可靠的数据存储、高性能和强大的功能,成为最受欢迎的数据库之一。
Excel是一种非常灵活的电子表格软件,用它可以存储各种数据,本节将对如何将Excel导入SQL Server2000数据库进行详细介绍。 开发环境:NET2.0 开发工具:vs2005 开发语言:c# 数据库:server2000