现在有这些资料,employee_list表中unit_id与dept_id都准确赋值了,
--例x0000001 张三 男 电子0901班 00000001 NULL 10 01 0 0
unit_id=10,dept_id=01,而so_id还没有赋值,因为这些记录都是新纪录,现需要编写脚本,用于实现
1.通过unit_id=10,dept_id=01在so表查看是否存在电子1班,
如果有则以那条记录的so更新掉employee_list的so_id
如果不存在,则在unit_id=10,dept_id=01条件下,在so表中添加一条记录。--MAX(so_id)+1
--例10 01 01 电子0801班
----10 01 02 电子0802班
----10 01 03 电子0803班
则添加
----10 01 04 电子0901班
同时,更新对应employee_list的so_id=04
create table employee_list
(
employeeid varchar(20) not null,
[name] varchar(12) not null,
Sex char(2) not null,
departname varchar(50) not null,
cardid varchar(20) not null,
bz varchar(20) null,
unit_id char(2) not null,
dept_id char(2) not null,
si_id char(2) not null,
RecordMark int not null,
)create table so
(
unit_id char(2) not null,
dept_id char(2) not null,
so_id char(2) not null,
so_name varchar(30) not null,
)本人思路通过查询employee_list表得到departname ,unit_id,dept_id
去so表查询,判断departname 存在与否,
不存在则,再取对应’部门‘so_id最大值 +1 添加1条新的的so记录
再将so_id+1 值取出,更新员工表,反之直接取值更新。不知道好坏,也不知道除了补零外还要考虑什么,想看看高手们的思路。我继续酝酿ing...
--例x0000001 张三 男 电子0901班 00000001 NULL 10 01 0 0
unit_id=10,dept_id=01,而so_id还没有赋值,因为这些记录都是新纪录,现需要编写脚本,用于实现
1.通过unit_id=10,dept_id=01在so表查看是否存在电子1班,
如果有则以那条记录的so更新掉employee_list的so_id
如果不存在,则在unit_id=10,dept_id=01条件下,在so表中添加一条记录。--MAX(so_id)+1
--例10 01 01 电子0801班
----10 01 02 电子0802班
----10 01 03 电子0803班
则添加
----10 01 04 电子0901班
同时,更新对应employee_list的so_id=04
create table employee_list
(
employeeid varchar(20) not null,
[name] varchar(12) not null,
Sex char(2) not null,
departname varchar(50) not null,
cardid varchar(20) not null,
bz varchar(20) null,
unit_id char(2) not null,
dept_id char(2) not null,
si_id char(2) not null,
RecordMark int not null,
)create table so
(
unit_id char(2) not null,
dept_id char(2) not null,
so_id char(2) not null,
so_name varchar(30) not null,
)本人思路通过查询employee_list表得到departname ,unit_id,dept_id
去so表查询,判断departname 存在与否,
不存在则,再取对应’部门‘so_id最大值 +1 添加1条新的的so记录
再将so_id+1 值取出,更新员工表,反之直接取值更新。不知道好坏,也不知道除了补零外还要考虑什么,想看看高手们的思路。我继续酝酿ing...
先这样理解,写下SQL吧。
插入的存储过程:if(object_id('InsertSoOrUpdatEmployee')
drop procedure InsertSoOrUpdatEmployee
gocreate procedure InsertSoOrUpdatEmployee
@unit_id int,@dept_id int,@departname varchar(50)
as
declare @soid int
select @soid=so_id from so where unit_id=@unit_id and dept_id=@dept_id and so_name=@departname
if(@soid is null)
insert into so
select @unit_id,@dept_id,isnull(max(so_id),0)+1,@departname
from so where unit_id=@unit_id and dept_id=@dept_id
--else 如果只插入不更新的话
update Employee_list set so_id=@soid where unit_id=@unit_id and dept_id=@dept_id and departname=@departname
if exists(select 1 from so where unit_id='10' and dept_id='01' and so_name=N'电子1班')
begin
update a set a.so_id=b.so_id from employee_list a,so b
where a.unit_id='10' and a.dept_id='01' and b.so_name=N'电子1班'
a.unit_id=b.unit_id and a.dept_id=b.dept_id
end
else
begin
declare @maxso varchar(20)
select @maxso=max(so_id) from so
insert into so
select unit_id,dept_id,cast(@maxso as int)+1,departname from
end
(
employeeid varchar(20) not null,
[name] varchar(12) not null,
Sex char(2) not null,
departname varchar(50) not null,
cardid varchar(20) not null,
bz varchar(20) null,
unit_id char(2) not null,
dept_id char(2) not null,
si_id char(2) not null, ---这里应该是so_id
RecordMark int not null,
)
if object_id('employee_list') is not null
drop table employee_list
create table employee_list
(
employeeid varchar(20) not null,
[name] varchar(12) not null,
Sex char(2) not null,
departname varchar(50) not null,
cardid varchar(20) not null,
bz varchar(20) null,
unit_id char(2) not null,
dept_id char(2) not null,
so_id char(2) not null,
RecordMark int not null,
)if object_id('so') is not null
drop table so
create table so
(
unit_id char(2) not null,
dept_id char(2) not null,
so_id char(2) not null,
so_name varchar(30) not null,
)
insert into employee_list
select 'x0000001',N'张三', N'男', N'电子0901班', '00000001', NULL, '10', '01', '0', 0insert into so
select '10', '01', '01', N'电子0801班' union all
select '10', '01', '02', N'电子0802班' union all
select '10', '01', '03', N'电子0803班' select * from employee_list where unit_id=10 and dept_id=01select * from so where unit_id='10' and dept_id='01'if exists(select 1 from so where unit_id='10' and dept_id='01' and so_name=N'电子1班')
begin
update a set a.so_id=b.so_id from employee_list a,so b
where a.unit_id='10' and a.dept_id='01' and b.so_name=N'电子1班' and
a.unit_id=b.unit_id and a.dept_id=b.dept_id
end
else
begin
declare @maxso varchar(20)
select @maxso=max(so_id) from so
insert into so
select unit_id,dept_id,cast(@maxso as int)+1,departname
from employee_list
where unit_id='10' and dept_id='01'
end
select * from so
update ...
else
insert ...
declare @unit_id varchar(20) --employee_list——10
declare @dept_id varchar(20) --employee_list——03
declare employee cursor for
select distinct departname,unit_id,dept_id from employee_list
open employee
fetch next from employee into @departname,@unit_id,@dept_id
while (@@fetch_status = 0)
beginif exists(select 1 from so where unit_id=@unit_id and dept_id=@dept_id and so_name=@departname)
begin
update a set a.so_id=b.so_id from employee_list a,so b
where b.unit_id=@unit_id and b.dept_id=@dept_id and b.so_name=@departname
end
else
begin
declare @maxso int
select @maxso=max(so_id)+1 from so where unit_id=@unit_id and dept_id=@dept_id
--print(@maxso)
declare @so char(2)
if len(@maxso)<2
begin
set @so=Right('0000'+convert(varchar(2),@maxso),2)
end
--print(@so)
begin tran aa
insert into so
select unit_id,dept_id,@so,departname from employee_list
update a set a.so_id=b.so_id from employee_list a,so b
where b.unit_id=@unit_id and b.dept_id=@dept_id and b.so_name=@departname
if @@Error<>0
begin
Rollback tran aa
declare @message varchar(50)
Set @message='失败!'
RaisError(@message,16,2)
end
else
Commit Tran aa
end
fetch next from employee into @departname,@unit_id,@dept_id
end
close employee
deallocate employee 服务器: 消息 2627,级别 14,状态 1,行 30
违反了 PRIMARY KEY 约束 'PK_so'。不能在对象 'so' 中插入重复键。
语句已终止。(所影响的行数为 0 行)服务器: 消息 2627,级别 14,状态 1,行 30
违反了 PRIMARY KEY 约束 'PK_so'。不能在对象 'so' 中插入重复键。
语句已终止。(所影响的行数为 0 行)服务器: 消息 2627,级别 14,状态 1,行 30
违反了 PRIMARY KEY 约束 'PK_so'。不能在对象 'so' 中插入重复键。
语句已终止。(所影响的行数为 0 行)
谢谢大虾们帮忙,
让我学到不少,
以上为现在的脚本,存在错误,希望大虾帮忙一起找找错误
declare @dept_id char(2)
declare @so_id char(2)
declare @so_Name varchar(30)
declare @msg varchar(100)declare so_cursor cursor for
select departname,unit_id,dept_id from employee_List
group by departname,unit_id,dept_idOPEN so_cursorFETCH NEXT FROM so_cursor
INTO @so_Name, @unit_id, @dept_idWHILE @@FETCH_STATUS = 0
BEGIN
begin tran so_tran
if Exists(select 1 from so where so_name=@so_Name)
begin
select @unit_id=unit_id, @dept_id=dept_id,@so_id=so_id from so where so_name=@so_Name
end
else
begin
select @so_id=right('00'+convert(varchar(2),isnull(Max(so_id),0)+1),2) from so where unit_id=@unit_id and dept_id=@dept_id
--select @so_id=right('00'+@so_id,2)
insert so select @unit_id,@dept_id,@so_id,@so_name
if @@Error<>0
begin
rollback tran so_tran
set @msg='unit_id:'+@unit_id+' dept_id:'+@dept_id+' so_id:'+@so_id+' so_name:'+@so_name+' so 表处理异常'
RAISERROR(@msg,16,2)
return
end
end update employee_list set so_id=@so_id
where unit_id=@unit_id and dept_id=@dept_id and departname=@so_name
if @@Error<>0
begin
rollback tran so_tran
set @msg='unit_id:'+@unit_id+' dept_id:'+@dept_id+' so_id:'+@so_id+' so_name:'+@so_name+' emplyee_list 表处理异常'
RAISERROR(@msg,16,2)
return
end commit tran so_tran FETCH NEXT FROM so_cursor
INTO @so_Name, @unit_id, @dept_id
ENDCLOSE so_cursor
DEALLOCATE so_cursor