对具有主键的表同时和其它具有关联关系的表的异数据库之间的复制
具体思路如下:
1.将表的主键标识打开,以便可以实现操作
2.如果两个表之间存在着主键与外键的关联,在删除的时候,首先要先删外键,然后再删主键
3.现在可以实行对表的具体操作了
4.重新建立关联,先建主键,再外键
5.关闭表的主键的标识
具体实例如下:
SET IDENTITY_INSERT t_recipe ON
ALTER TABLE t_recipe_detail
DROP CONSTRAINT FK_T_RECIPE_REFERENCE_T_RECIPE
ALTER TABLE t_recipe DROP CONSTRAINT PK_T_RECIPE
delete from t_recipe where id =23303
insert into t_recipe (ID,clinicno,patientId,docId,recipeDateTime,opterId,deptId,type,txflag,jfId,state,sflag,amount,payflag,payDateTime,isreturn,returnDateTime,dropDateTime,diagInfo,delDateTime)
select ID,clinicno,patientId,docId,recipeDateTime,opterId,deptId,type,txflag,jfId,state,sflag,amount,payflag,payDateTime,isreturn,returnDateTime,dropDateTime,diagInfo,delDateTime from OPENDATASOURCE('SQLOLEDB','Data Source=127.0.0.1;User ID=sa;Password=123456')
.wys.dbo.t_recipe where id =23303
ALTER TABLE t_recipe
ADD CONSTRAINT PK_T_RECIPE PRIMARY KEY (ID)
alter table t_recipe_detail
add constraint FK_T_RECIPE_REFERENCE_T_RECIPE foreign key (recipeId)
references t_recipe (ID)
set IDENTITY_INSERT t_recipe OFF
1.将表的主键标识打开,以便可以实现操作
2.如果两个表之间存在着主键与外键的关联,在删除的时候,首先要先删外键,然后再删主键
3.现在可以实行对表的具体操作了
4.重新建立关联,先建主键,再外键
5.关闭表的主键的标识
具体实例如下:
SET IDENTITY_INSERT t_recipe ON
ALTER TABLE t_recipe_detail
DROP CONSTRAINT FK_T_RECIPE_REFERENCE_T_RECIPE
ALTER TABLE t_recipe DROP CONSTRAINT PK_T_RECIPE
delete from t_recipe where id =23303
insert into t_recipe (ID,clinicno,patientId,docId,recipeDateTime,opterId,deptId,type,txflag,jfId,state,sflag,amount,payflag,payDateTime,isreturn,returnDateTime,dropDateTime,diagInfo,delDateTime)
select ID,clinicno,patientId,docId,recipeDateTime,opterId,deptId,type,txflag,jfId,state,sflag,amount,payflag,payDateTime,isreturn,returnDateTime,dropDateTime,diagInfo,delDateTime from OPENDATASOURCE('SQLOLEDB','Data Source=127.0.0.1;User ID=sa;Password=123456')
.wys.dbo.t_recipe where id =23303
ALTER TABLE t_recipe
ADD CONSTRAINT PK_T_RECIPE PRIMARY KEY (ID)
alter table t_recipe_detail
add constraint FK_T_RECIPE_REFERENCE_T_RECIPE foreign key (recipeId)
references t_recipe (ID)
set IDENTITY_INSERT t_recipe OFF