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_*_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