SQL2000 database synchronization, write storage process is not running. Please m

A database is HWATT, from the HWATT database to the user into the kaoqin database.
CREATE PROCEDURE INSERTUSER AS
begin
declare @userid int
declare @username int
declare @count int
declare @userkaoname varchar
declare @sqlstr varchar
set @count = 0
select @count = count(*) from HWATT.dbo.KQZ_Employee where Birthday between dateadd(wk,datediff(wk,0,getdate()),0) and getdate()
while @count>0
begin
select @sqlstr = 'select top('+convert(varchar,@count)+') '+convert(varchar,@userid)+' = EmployeeCode, '+convert(varchar,@userkaoname)+' = EmployeeName from HWATT.dbo.KQZ_Employee where Birthday between dateadd(wk,datediff(wk,0,getdate()),0) and getdate()'
exec (@sqlstr)
select @username = userid from userinfo where BADGENUMBER = @userid
if(@username<0)
begin
insert into USERINFO(BADGENUMBER,NAME,DEFAULTDEPTID,ATT,INLATE,OUTEARLY,OVERTIME,SEP,HOLIDAY,LUNCHDURATION,privilege,InheritDeptSch,InheritDeptSchClass,AutoSchPlan,MinAutoSchInterval,RegisterOT,InheritDeptRule,EMPRIVILEGE) VALUES(@userid,@userkaoname,'1','1','1','1','1','1','1','1','0','1','1','1','24','1','1','0')
set @count = convert(int,@count)-1
end
set @count = convert(int,@count)-1
end
end
GO

Started by Angelia at November 22, 2016 - 1:53 PM

Circular insert data, but the cycle is not running. Each time the implementation of the 1 data.

Posted by Angelia at December 04, 2016 - 2:49 PM

You should use EmployeeCode to determine the cycle cycle, or use the cursor, you approach is not desirable

Posted by Enoch at December 17, 2016 - 2:58 PM

Can write an example? I'm not in database!

Posted by Angelia at December 18, 2016 - 3:20 PM

It means EmployeeCode is empty to determine the cycle is not?

Posted by Angelia at December 28, 2016 - 4:12 PM

Your program is very simple, don't need to use cycle, give you changed:

CREATE PROCEDURE INSERTUSER  
AS
set nocount on 

begin try
	insert into USERINFO(BADGENUMBER,NAME,DEFAULTDEPTID,ATT,INLATE,OUTEARLY,OVERTIME,SEP,HOLIDAY,LUNCHDURATION,privilege,
			InheritDeptSch,InheritDeptSchClass,AutoSchPlan,MinAutoSchInterval,RegisterOT,InheritDeptRule,EMPRIVILEGE) 
	select a.EmployeeCode,a.EmployeeName,'1','1','1','1','1','1','1','1','0','1','1','1','24','1','1','0'
	from HWATT.dbo.KQZ_Employee a
	inner join userinfo b on a.EmployeeCode=b.BADGENUMBER  -->Must be connected with the inner join
	where Birthday between dateadd(wk,datediff(wk,0,getdate()),0) and getdate()
	select 'Successful operation'
end try
begin catch
	select 'Operation failed'
end catch

GO 

Posted by Enoch at January 04, 2017 - 6:27 PM

EmployeeCode is not null, this is the foreground program needs to do judgment, only not empty before they can be sent to the back-end storage.

Posted by Enoch at January 06, 2017 - 5:51 PM

But BADGENUMBER is a primary key, cannot repeat insertion. The data in the KQZ_Employee table part is already in USERINFO. When do loop is to judge repeated data!

Posted by Angelia at January 09, 2017 - 5:31 PM

Master to solve! Puzzle for a long time.!

Posted by Angelia at January 12, 2017 - 4:36 PM

CREATE PROCEDURE INSERTUSER  
AS
set nocount on 

begin try
	insert into USERINFO(BADGENUMBER,NAME,DEFAULTDEPTID,ATT,INLATE,OUTEARLY,OVERTIME,SEP,HOLIDAY,LUNCHDURATION,privilege,
			InheritDeptSch,InheritDeptSchClass,AutoSchPlan,MinAutoSchInterval,RegisterOT,InheritDeptRule,EMPRIVILEGE) 
	select a.EmployeeCode,a.EmployeeName,'1','1','1','1','1','1','1','1','0','1','1','1','24','1','1','0'
	from HWATT.dbo.KQZ_Employee a
	inner join userinfo b on a.EmployeeCode=b.BADGENUMBER  -->Must be connected with the inner join
	where Birthday between dateadd(wk,datediff(wk,0,getdate()),0) and getdate()
		and not exists (select 1 from USERINFO c where a.EmployeeCode=c.BADGENUMBER)  -->With this you judge filter out duplicate 
	select 'Successful operation'
end try
begin catch
	select 'Operation failed'
end catch

GO 


Posted by Enoch at January 14, 2017 - 5:19 PM