日期:2014-05-18  浏览次数:20527 次

请教数据更新问题
MSSQL数据库A和数据库B中都有二个相同的表:POS(ID,FormDate),PODetails(ID,FormID)。其中列ID为IDentity列,FormID关联POS的ID列。现想设置一个参数@FORMDATE,把数据库A中@FormDate=FormDate的POS表及PODetails表的数据导入到数据库B中。请问如何实现(MSSQL的Replication能实现吗),谢谢!

------解决方案--------------------
如果A、B库是在同一台机器中,可以直接使用以下语句
SQL code
insert into B.dbo.PODetails(FormID)
select distinct ID from A.dbo.POS 
  where FormDate=@FormDate

------解决方案--------------------
SET IDENTITY_INSERT b..POS ON

insert into b..POS(id , FormDate) select id , FormDate from a..pos where FormDate = @FormDate

SET IDENTITY_INSERT b..POS OFF

SET IDENTITY_INSERT b..PODetails ON

insert into b..PODetails(id , formid) select m.id , m.formid from a..PODetails m,a..pos n where m.formid = n.id and n.formdate = @formdate

SET IDENTITY_INSERT b..PODetails OFF