--更新 update a set a.什么=b.什么,a.什么2=b.什么2 from moontemp a inner join moonjxcmx b on a.onlyid=b.onlyid--插入 insert into moonjxcmx(字段1,字段2,字段3) select 字段1,字段2,字段3 from moontemp t where not exists( select * from moonjxcmx where onlyid=t.onlyid )
UPDATE B SET col1=A.col1, col2=A.col2 FROM moontemp AS A JOIN moonjxcmx AS B ON A.onlyid=B.onlyid;INSERT moonjxcmx SELECT * FROM moontemp AS A LEFT JOIN moonjxcmx AS B ON A.onlyid=B.onlyid WHERE B.onlyid IS NULL;
也就是 moontemp所有记录+仅moonjxcmx有的记录 这么做效率更高一点吧
delete moonjxcmx from moontemp where moonjxcmx.onlyId=moontemp.onlyidinsert into moonjxcmx(字段1,字段2,字段3,...) select (字段a,字段b,字段c,...) from moontemp
如果想一个SQL语句就实现,有难度,还不如写个PROCEDURE,需要吗?
CREATE PROCEDURE UpdateMoonjxmxFromMoontemp AS Declare @IsExists Int = 0 Declare @OnlyID Varchar(50)Declare OnlyID_Cursor Cursor For Select OnlyID From moontemp Open OnlyID Fetch Next From OnlyID_Cursor Into @OnlyID While @@Fetch_Status = 0 Begin Select @IsExists = Count(OnlyID) From moonjxcmx Where OnlyID = @OnlyID
If(@IsExists > 0 ) Begin Update moonjxcmx Set Column1 = B.Column1,Column2 = B.Column2 From moonjxcmx A,moontemp B Where A.OnlyID = B.OnlyID End
Else Begin Insert Into moonjxcmx Select * From moontemp Where OnlyID = @OnlyID End
Fetch Next From OnlyID_Cursor Into @OnlyID EndClose OnlyID_Cursor DEALLOCATE OnlyID_Cursor Execute UpdateMoonjxmxFromMoontempPs:上面的做法就是比较严格按着楼主的逻辑来处理,其实.可以简单的根据楼上好几楼的方式,直接 Update Or Insert
update a set a.什么=b.什么,a.什么2=b.什么2
from moontemp a inner join moonjxcmx b on a.onlyid=b.onlyid--插入
insert into moonjxcmx(字段1,字段2,字段3)
select 字段1,字段2,字段3 from moontemp t
where not exists(
select * from moonjxcmx where onlyid=t.onlyid
)
col1=A.col1,
col2=A.col2
FROM moontemp AS A
JOIN moonjxcmx AS B
ON A.onlyid=B.onlyid;INSERT moonjxcmx
SELECT *
FROM moontemp AS A
LEFT JOIN moonjxcmx AS B
ON A.onlyid=B.onlyid
WHERE B.onlyid IS NULL;
moontemp所有记录+仅moonjxcmx有的记录
这么做效率更高一点吧
from moontemp
where moonjxcmx.onlyId=moontemp.onlyidinsert into moonjxcmx(字段1,字段2,字段3,...)
select (字段a,字段b,字段c,...) from moontemp
AS
Declare @IsExists Int = 0
Declare @OnlyID Varchar(50)Declare OnlyID_Cursor Cursor For
Select OnlyID From moontemp
Open OnlyID
Fetch Next From OnlyID_Cursor Into @OnlyID
While @@Fetch_Status = 0
Begin
Select @IsExists = Count(OnlyID)
From moonjxcmx
Where OnlyID = @OnlyID
If(@IsExists > 0 )
Begin
Update moonjxcmx
Set Column1 = B.Column1,Column2 = B.Column2
From moonjxcmx A,moontemp B
Where A.OnlyID = B.OnlyID
End
Else
Begin
Insert Into moonjxcmx
Select * From moontemp
Where OnlyID = @OnlyID
End
Fetch Next From OnlyID_Cursor Into @OnlyID
EndClose OnlyID_Cursor
DEALLOCATE OnlyID_Cursor
Execute UpdateMoonjxmxFromMoontempPs:上面的做法就是比较严格按着楼主的逻辑来处理,其实.可以简单的根据楼上好几楼的方式,直接 Update Or Insert