/****** 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
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].PLMS_NDataSync ( @SyncResult INT OUT --处理结果 ) AS IF OBJECT_ID('tempdb.dbo.#T_UserA1','U') IS NOT NULL DROP TABLE dbo.#T_UserA1; IF OBJECT_ID('tempdb.dbo.#T_UserB2','U') IS NOT NULL DROP TABLE dbo.#T_UserB2; SELECT [TelePhone], [Password], [UserId], [Password2], [PeopleNo], [UserName], [IsWork], [MACAddress], [Region], [Department], [CZDepart], [Jobtitle],[AddTime],[LastTime] INTO dbo.#T_UserA1 FROM [dbo].[TB_NUsers]; WITH T_User AS (SELECT ROW_NUMBER() over(order by U.[PassWord]) [row_number],U.[UserID],U.[PassWord],U.[UserName], CASE WHEN D.[Telephone]<>'' THEN D.[Telephone] ELSE D.[TeleMobile] END PhoneNumber, D.IsWork,D.[PeopleName],D.[PeopleNo], D.[ZLDomain],D.[Workshop],D.[CZDepart],D.[ZhiChun] FROM [dbo].[TSUser] U left join [dbo].[TB_DeptPeople] D ON (U.UserName=D.PeopleName COLLATE Chinese_Taiwan_Stroke_CI_AS)) SELECT * INTO dbo.#T_UserB2 FROM T_User U WHERE not exists(SELECT [TelePhone] FROM [dbo].[TB_NUsers] N WHERE U.PhoneNumber=N.TelePhone) AND U.PhoneNumber IS NOT NULL; --select * from dbo.#T_UserB2 --定义变量 DECLARE @id NVARCHAR(50),@PhoneNumber NVARCHAR(50),@CZDepart NVARCHAR(50), @Jobtitle NVARCHAR(50),@Password VARCHAR(100),@UserId NVARCHAR(50),@PeopleNo NVARCHAR(20), @UserName NVARCHAR(100),@Region NVARCHAR(50),@Department NVARCHAR(50),@sql NVARCHAR(max) SELECT @id = MIN([row_number]) FROM dbo.#T_UserB2; WHILE @id IS NOT NULL BEGIN SELECT @PhoneNumber=PhoneNumber,@CZDepart=CZDepart,@Jobtitle=ZhiChun,@Password=[PassWord],@UserId=ltrim(rtrim(UserID)),@PeopleNo=PeopleNo, @UserName=UserName,@Region=ZLDomain,@Department=Workshop FROM dbo.#T_UserB2 WHERE [row_number]=@id; --定义变量 DECLARE @line INT =0 SELECT @line=COUNT(0) FROM dbo.#T_UserA1 WHERE TelePhone=@PhoneNumber; IF @line<=0 BEGIN SET @Password=UPPER(substring(sys.fn_sqlvarbasetostr(HashBytes('MD5',ltrim(rtrim(@Password)))),3,32)) set @sql='INSERT INTO [dbo].[TB_NUsers](TelePhone,CZDepart,Jobtitle,[Password],[Password2],IsOther,PeopleNo,UserName,UserId,IsWork,Region,Department) VALUES ('''+@PhoneNumber+''','''+@CZDepart+''','''+@Jobtitle+''','''+@Password+''','''+@Password+''',1, '''+@PeopleNo+''','''+@UserName+''','''+@UserId+''',1,'''+@Region+''','''+@Department+''')' print @sql; --INSERT INTO [dbo].[TB_NUsers](TelePhone,CZDepart,Jobtitle,[Password],[Password2],IsOther,PeopleNo,UserName,UserId,IsWork,Region,Department) -- VALUES -- (@PhoneNumber,@CZDepart,@Jobtitle,@Password,@Password,1, -- @PeopleNo,@UserName,@UserId,1,@Region,@Department); END SELECT @id = MIN([row_number]) FROM dbo.#T_UserB2 WHERE [row_number] >@id END IF OBJECT_ID('tempdb.dbo.#T_UserA1','U') IS NOT NULL DROP TABLE dbo.#T_UserA1 IF OBJECT_ID('tempdb.dbo.#T_UserB2','U') IS NOT NULL DROP TABLE dbo.#T_UserB2
/*============================================================================== * * Filename: sqllist.sql * Description: sql 根据指定条件获取一个字段批量获取数据插入另外一张表字段中 * Version: 1.0 * Created: 2016.03.13 * Author : XuChu.SUN * E-mail : codelife@vip.qq.com * Q Q : 592568532 * Profile Url: * Company: Copyright (C) Create Family Wealth Power By Peter * ============================================================