--增加不存在的 insert tb select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] where id not in (select id from tb) --更新存在的 update tb set col=b.col from tb a, (OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]) b where a.id=b.id
--更正,更新存在的 update tb set col=b.col from tb a, OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] b where a.id=b.id
if exists(select 1 from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] where id not in (select id from tb) ) insert tb select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] where id not in (select id from tb) else update tb set col=b.col from tb a, OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] b where a.id=b.id
update product set product_name=b.product_name from product a, OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="D:\上传数据\刻花八角片.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[点状刻花八角片$] b where a.product_id=b.product_id 按照你们的方法全部都刷新了,怎么回事??
SELECT *
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source= "c:\test.xls ";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions
insert tb select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
where id not in (select id from tb)
--更新存在的
update tb set col=b.col from tb a, (OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]) b where a.id=b.id
update tb set col=b.col from tb a, OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] b where a.id=b.id
(product_id, big_class, small_class, product_name, product_name_en, shape, product_size, style_class, color_id, colorname, colorname_en, unit,units_rate, pro_price, pro_price_en, pro_cost, hand_fee, spoilage, gweight, inner_carton, moq, pic, grade, material, belongs_who, update_date,
addtime, flag, issale, description, en_description,pro_cost_en,ifdel,hand_fee_en)
SELECT product_id, big_class, small_class, product_name, product_name_en, shape, product_size, style_class, color_id, colorname, colorname_en, unit,
units_rate, pro_price, pro_price_en, pro_cost, hand_fee, spoilage, gweight, inner_carton, moq, pic, grade, material, belongs_who, update_date,
addtime, flag, issale, description, en_description,pro_cost_en,ifdel,hand_fee_en
FROM OPENROWSET('microsoft.jet.oledb.4.0',
'Excel 4.0;HDR=yes;database=D:\上传数据\刻花八角片.xls', 点状刻花八角片$) AS derivedtbl_1
以上是全部插入语句,可以使用,怎么实现相同的product_id的话就更新此条记录,否则就添加!!!
insert tb select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] where id not in (select id from tb)
else
update tb set col=b.col from tb a, OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] b where a.id=b.id
按照你们的方法全部都刷新了,怎么回事??