SQL 笔记

SQL 笔记 Powered By:记得忘记  21:18 2006-9-3

新建链接服务器
设置"远程登录"用户名:
设置"使用密码"

INSERT INTO [10.140.68.7].YYERP.dbo.T_OutByMachineDetail
SELECT * FROM dbo.T_OutByMachineDetail
SELECT * FROM [10.140.68.7].YYERP.dbo.T_OutByMachineDetail

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[usp_DelRepeat] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
drop   procedure   [ dbo ] . [ usp_DelRepeat ]
GO

SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO


-- 删除重复记录、并提交结果集
-- Powered By:CHENQP
CREATE   PROCEDURE   [ dbo ] . [ usp_DelRepeat ]
   
AS
       
select   identity ( int , 1 , 1 )  as  autoID,  *   into  #Tmp  from  dbo.T_OutByMachineDetail_temp

select   min (autoID)  as  autoID  into  #Tmp2  from  #Tmp  group   by  V_BarCode

select   *   into  #Tmp3
from  #Tmp  where  autoID  in ( select  autoID  from  #tmp2)
ORDER   BY  V_BARCODE


UPDATE  #Tmp3  SET  COL_RP_CODE  =   ' DY2006090101 ' ,
COL_RRD_CODE  =   ' p06000004 ' ,
COL_SEQUENCE_NUMBER  =   1 ,
V_IsCheck  =   NULL ,
N_GrossWeight  =   215.20

INSERT   INTO  dbo.T_OutByMachineDetail
SELECT  V_BarCode, V_BatchID, V_FlowNo, V_GBGradeCode, I_PieceCount, N_GrossWeight, N_ProuOutWeight, I_OriginID, V_BoxID, I_PurchaseID, V_PackStyle, I_ShapeCode, C_CustCode, D_Date, V_TeamTimesID, V_LineCode, V_ContainerNo, I_TaskID, I_MonitorID, V_Operate, V_ISReturn, V_IsCheck, V_IsGet, V_ProuYear, C_Type, I_TLProcTypeCode, V_PLSecern, V_Remark, IsDeleted, IsChecked, COL_RP_CODE, COL_RRD_CODE, COL_SEQUENCE_NUMBER
  FROM  #Tmp3
ORDER   BY  V_BARCODE
    GO