/******1.创建本地视图，否则每一次访问都要写好长的SQL语句******/
create view TJ_HIS_PRODUCT AS (select * from openquery(TJ_HIS_TEST,
'SELECT * FROM HR.V_BJP_INTERFACE_MEDICINEINFO'))
/******2.存储过程细节******/
USE [库]
GO
/****** Object: StoredProcedure
[dbo].[SysUpdateProductnew] Script Date: 12/02/2016 10:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SysUpdateProductnew]
AS
/*
第一步插入新数据
*/
DECLARE @DRUG_CODE varchar(20),@goodsid varchar(20),@maker_id varchar(50),@spec varchar(50),@DRUG_NAME varchar(150),@unit varchar(10),
@styleid int,@pieceratio VARCHAR(20),@makername varchar(150),@Mnemonic varchar(30),@rtprice numeric(18, 4),@wsprice numeric(18, 4),@TOXI_PROPERTY VARCHAR(20),@min_spec varchar(20),@v_sub_package int,@mini_unit varchar(30),@ExtFileds5 varchar(5)
declare Sys_product_cur scroll cursor for
select distinct DRUG_CODE,
DRUG_NAME,
INPUT_CODE,
DRUG_SPEC,
DRUG_UNITS,
TRADE_PRICE,
RETAIL_PRICE,
FIRM_ID,
FIRM_NAME,
TOXI_PROPERTY,
MIN_SPEC,
AMOUNT_PER_PACKAGE,
Min_units,
Drug_INDICATOR
FROM TJ_HIS_PRODUCT THP
WHERE NOT EXISTS(SELECT 1
FROM PRODUCT
WHERE PRODUCT.drug_id = THP.DRUG_CODE
AND PRODUCT.SPEC = THP.DRUG_SPEC
AND PRODUCT.MAKERID = THP.FIRM_ID
AND PRODUCT.UNIT = THP.DRUG_UNITS)
open Sys_product_cur
fetch first from Sys_product_cur into @DRUG_CODE,@DRUG_NAME,@Mnemonic,@spec,@unit,
@wsprice ,@rtprice ,@maker_id,@makername ,@TOXI_PROPERTY ,@min_spec,@v_sub_package,@mini_unit,@ExtFileds5
while @@fetch_status=0
BEGIN
--10位商品编码
select @goodsid=right('0000000000'+cast(isnull(max(goodsid),0)+1 as varchar(10)),10) from product
INSERT into product(warehouseid,goodsid,Description,spec,unit,styleid,ControlId,
PieceRatio,MidRatio,AbcCode,MakerId,MakerName,Mnemonic,CaseScale,
RtPrice,WsPrice,CreateDate,CreateMan,ModifiedDate,drug_id,status,orgid,ExtFields1,ExtFileds2,ExtFileds3,MiniUnit,ExtFileds5)
values ('Y30',@goodsid,@DRUG_NAME,@spec,@unit,@styleid,0,
@pieceratio,0,'C',@maker_id,@makername,@Mnemonic,0,
@rtprice,@wsprice,getdate(),'SA',getdate(),@DRUG_CODE,0,'102',@TOXI_PROPERTY,@min_spec,@v_sub_package,@mini_unit,@ExtFileds5)
fetch next from Sys_product_cur into @DRUG_CODE,@DRUG_NAME,@Mnemonic,@spec,@unit,
@wsprice ,@rtprice ,@maker_id,@makername ,@TOXI_PROPERTY ,@min_spec,@v_sub_package,@mini_unit,@ExtFileds5
END
close Sys_product_cur
deallocate Sys_product_cur
--第二步，修改已存商品的信息
BEGIN
update product
SET PRODUCT.PieceRatio = '1',
PRODUCT.RTPRICE = TJ_HIS_PRODUCT.RETAIL_PRICE,
PRODUCT.WsPrice = TJ_HIS_PRODUCT.TRADE_PRICE,
PRODUCT.BuyPrice = TJ_HIS_PRODUCT.TRADE_PRICE,
PRODUCT.MiniUnit = TJ_HIS_PRODUCT.MIN_UNITS,--最小单位
PRODUCT.ExtFileds3 = TJ_HIS_PRODUCT.AMOUNT_PER_PACKAGE,
PRODUCT.Description = TJ_HIS_PRODUCT.DRUG_NAME,
PRODUCT.ExtFileds5 = TJ_HIS_PRODUCT.DRUG_INDICATOR,
product.Mnemonic = TJ_HIS_PRODUCT.INPUT_CODE
from TJ_HIS_PRODUCT,product
where TJ_HIS_PRODUCT.DRUG_CODE = product.drug_id
AND TJ_HIS_PRODUCT.DRUG_SPEC = product.Spec
AND TJ_HIS_PRODUCT.DRUG_UNITS = product.Unit
AND TJ_HIS_PRODUCT.FIRM_ID = product.MakerId
END
--3.过滤停用信息
BEGIN
UPDATE Product SET status = '9' FROM Product WHERE status<>'9' AND NOT EXISTS(
SELECT * FROM TJ_HIS_PRODUCT V WHERE Product.drug_id = V.DRUG_CODE AND PRODUCT.Spec = V.DRUG_SPEC AND PRODUCT.Unit = V.DRUG_UNITS AND PRODUCT.MakerId = V.FIRM_ID)
END
欢迎技术大拿留下建议！不喜误喷，谢谢！