create proc 过程名 as declare @a int insert into T_Father select * from T_Father where 条件.. select @a=@@identity insert into T_Son select * from T_Son where F_ID=@a
执行这句话时会出错 insert into T_Father select * from T_Father where f_id=1出错如下: 仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'T_Father'中为标识列指定显式值。
create proc 过程名 as declare @a int insert into T_Father(F_Name,F_Code) select F_Name,F_Code from T_Father where 条件.. select @a=@@identity insert into T_Son (F_ID,S_Name,S_Code) select F_ID,S_Name,S_Code from T_Son where F_ID=@a
--改成这样吧 create proc proc_tn as declare @a int insert into T_Father(F_Name,F_Code) select F_Name,F_Code from T_Father where F_ID=1 select @a=@@identity-1 insert into T_Son (F_ID,S_Name,S_Code) select F_ID,S_Name,S_Code from T_Son where F_ID=@a
测试: --表内容: create table T_Father(F_ID int identity(1,1), F_Name varchar(10), F_Code varchar(10)) insert into T_Father(F_Name,F_code) select 'Name1','Code1'create table 子表(S_ID int identity(1,1),F_ID int, S_Name varchar(10), S_Code varchar(10)) insert into T_Son(F_ID,S_Name,S_Code) select 1,'SName1','SCode1' union all select 1,'SName2','SCode2'--创建过程 create proc proc_tn as declare @a int insert into T_Father(F_Name,F_Code) select F_Name,F_Code from T_Father where F_ID=1 select @a=@@identity-1 insert into T_Son (F_ID,S_Name,S_Code) select F_ID,S_Name,S_Code from T_Son where F_ID=@a--执行exec proc_tn--结果: T_Father 1 Name1 Code1 2 Name1 Code1T_Son 1 1 SName1 SCode1 2 1 SName2 SCode2 3 1 SName1 SCode1 4 1 SName2 SCode2
存储过程如下: Create Proc sp_SelfCopy @IntFatherID int=0 as Declare @NewID int Insert Into T_Father Select F_Name,F_Code From T_Father Where F_ID=@IntFatherID Select @NewID=@@Identity Insert Into T_Son Select F_ID=@NewID,S_Name,S_Code From T_Son Where F_ID=@IntFatherID执行: exec sp_SelfCopy 1结果: T_Father 1 Name1 Code1 2 Name1 Code1T_Son 1 1 SName1 SCode1 2 1 SName2 SCode2 3 2 SName1 SCode1 4 2 SName2 SCode23、4条的数据略有差别,呵呵PS:这是小弟的第一个存储过程^_^,谢谢各位了!
T_Son-->T_Son
也就是同表复制了,标题里有的
as
declare @a int
insert into T_Father select * from T_Father where 条件..
select @a=@@identity
insert into T_Son select * from T_Son where F_ID=@a
insert into T_Father select * from T_Father where f_id=1出错如下:
仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'T_Father'中为标识列指定显式值。
as
declare @a int
insert into T_Father(F_Name,F_Code) select F_Name,F_Code from T_Father where 条件..
select @a=@@identity
insert into T_Son (F_ID,S_Name,S_Code) select F_ID,S_Name,S_Code from T_Son where F_ID=@a
你的答案已经接近我的要求了,我吃完饭晚上再试试看,到时我把自己做的也贴出来。我的要求可以用一个例子来说明一下
主表有一条记录
*******************************
F_ID F_Name F_Code
1 Name1 Code1
*******************************
子表相对应有二条记录
*******************************
S_ID F_ID S_Name S_Code
1 1 SName1 SCode1
2 1 SName2 SCode2
*******************************执行完存储过程后,如下:
主表有二条记录
*******************************
F_ID F_Name F_Code
1 Name1 Code1
2 Name1 Code1
*******************************
子表相对应有四条记录
*******************************
S_ID F_ID S_Name S_Code
1 1 SName1 SCode1
2 1 SName2 SCode2
3 2 SName1 SCode1
4 2 SName2 SCode2
*******************************
create proc proc_tn
as
declare @a int
insert into T_Father(F_Name,F_Code) select F_Name,F_Code from T_Father where F_ID=1
select @a=@@identity-1
insert into T_Son (F_ID,S_Name,S_Code) select F_ID,S_Name,S_Code from T_Son where F_ID=@a
--表内容:
create table T_Father(F_ID int identity(1,1), F_Name varchar(10), F_Code varchar(10))
insert into T_Father(F_Name,F_code) select 'Name1','Code1'create table 子表(S_ID int identity(1,1),F_ID int, S_Name varchar(10), S_Code varchar(10))
insert into T_Son(F_ID,S_Name,S_Code) select 1,'SName1','SCode1'
union all select
1,'SName2','SCode2'--创建过程
create proc proc_tn
as
declare @a int
insert into T_Father(F_Name,F_Code) select F_Name,F_Code from T_Father where F_ID=1
select @a=@@identity-1
insert into T_Son (F_ID,S_Name,S_Code) select F_ID,S_Name,S_Code from T_Son where F_ID=@a--执行exec proc_tn--结果:
T_Father
1 Name1 Code1
2 Name1 Code1T_Son
1 1 SName1 SCode1
2 1 SName2 SCode2
3 1 SName1 SCode1
4 1 SName2 SCode2
Create Proc sp_SelfCopy
@IntFatherID int=0
as
Declare @NewID int
Insert Into T_Father Select F_Name,F_Code From T_Father Where F_ID=@IntFatherID
Select @NewID=@@Identity
Insert Into T_Son Select F_ID=@NewID,S_Name,S_Code From T_Son Where F_ID=@IntFatherID执行:
exec sp_SelfCopy 1结果:
T_Father
1 Name1 Code1
2 Name1 Code1T_Son
1 1 SName1 SCode1
2 1 SName2 SCode2
3 2 SName1 SCode1
4 2 SName2 SCode23、4条的数据略有差别,呵呵PS:这是小弟的第一个存储过程^_^,谢谢各位了!