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

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[usp_DelRepeat]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[usp_DelRepeat]
GO

SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


--删除重复记录、并提交结果集
--PoweredBy:CHENQP
CREATEPROCEDURE[dbo].[usp_DelRepeat]

AS

selectidentity(int,1,1)asautoID,*into#Tmpfromdbo.T_OutByMachineDetail_temp

selectmin(autoID)asautoIDinto#Tmp2from#TmpgroupbyV_BarCode

select*into#Tmp3
from#TmpwhereautoIDin(selectautoIDfrom#tmp2)
ORDERBYV_BARCODE


UPDATE#Tmp3SETCOL_RP_CODE='DY2006090101',
COL_RRD_CODE='p06000004',
COL_SEQUENCE_NUMBER=1,
V_IsCheck=NULL,
N_GrossWeight=215.20

INSERTINTOdbo.T_OutByMachineDetail
SELECTV_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
ORDERBYV_BARCODE
GO