根据指定条件获取一个字段批量获取数据插入另外一张表代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】根据指定条件获取一个字段批量获取数据插入另外一张表

作者:liangjw0504 / 发布于2015/9/22/ 835


	/****** Object: StoredProcedure [dbo].[getSplitValue] Script Date: 03/13/2014 13:58:12 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	
	
	ALTER PROCEDURE [dbo].[getSplitValue]
	AS
	 --定义获取GUID
	 DECLARE @NEWID NVARCHAR(50)
	 SET @NEWID= REPLACE(NEWID(),'-','')
	
	 --判断临时表数据是否存在，如果存在则删除临时表
	if OBJECT_ID('tempdb..##project') is not null
	 DROP TABLE ##project
	 if OBJECT_ID('tempdb..##projectA') is not null
	 DROP TABLE ##projectA
	 --获取数据源信息
	 SELECT id,GuiGe,DocumentNO,OrderNO,WLNO,Color,ISFenMa INTO ##project FROM TB_FLChuKuMx
	 SELECT GuiGe,DocumentNO,OrderNO,WLNO,Color,ISFenMa INTO ##projectA FROM TB_FLKuCun WHERE OptType='领料'
	 --定义变量
	 DECLARE @id INT,@GuiGe NVARCHAR(50)
	 DECLARE @DocumentNO NVARCHAR(20),@OrderNO NVARCHAR(20),@WLNO NVARCHAR(20),@Color NVARCHAR(50),@ISFenMa NVARCHAR(20)
	 DECLARE @sql NVARCHAR(max)
	
	 SELECT @id = MIN(id) FROM ##project
	 WHILE @id IS NOT NULL BEGIN
	 SELECT @GuiGe=ISNULL(GuiGe,''),@DocumentNO=DocumentNO,@OrderNO=OrderNO,@WLNO=WLNO,@Color=Color,@ISFenMa=ISFenMa
	 FROM ##project WHERE id =@id
	 --定义变量
	 DECLARE @line INT =0
	 SELECT @line=COUNT(0) FROM ##projectA WHERE
	 DocumentNO=@DocumentNO AND OrderNO=@OrderNO AND WLNO=@WLNO AND Color=@Color AND ISFenMa=@ISFenMa
	
	 --如果查询出来数据则进行数据修改操作
	 IF @line >0 BEGIN
	 SET @sql ='update TB_FLKuCun set GuiGe= '''+@GuiGe+''' where
	 DocumentNO='''+@DocumentNO+''' and OrderNO='''+@OrderNO+'''
	 and WLNO='''+@WLNO+''' and Color='''+@Color+''' and ISFenMa='''+@ISFenMa+''' and OptType=''领料'' '
	 PRINT @sql
	 --EXEC(@sql)
	 END
	
	 SELECT @id = MIN(id) FROM dbo.##project WHERE id >@id
	
	 END
	 --判断临时表数据是否存在，如果存在则删除临时表
	 if OBJECT_ID('tempdb..##project') is not null
	 DROP TABLE ##project
	 if OBJECT_ID('tempdb..##projectA') is not null
	 DROP TABLE ##projectA

试试其它关键字

同语言下

可能有用的

liangjw0504贡献的其它代码(36)