多个表的数据转移到另外一个表中的存储过程

use TMS_DEV
if exists(select * from sysobjects where id=OBJECT_ID(N'USP_DataLoad_SubClient')
and OBJECTPROPERTY(id,N'IsProcedure')=1)
drop procedure USP_DataLoad_SubClient
go

create procedure USP_DataLoad_SubClient
@User int
as
declare @SourceSystemId int
select @SourceSystemId=SourceSystemId from T_CSC_SourceSystem where SourceSystemName='Hyperion'
begin

	update T_CSC_SubClient set
	UpdatedAt=getutcdate(),
	UpdatedBy=@User,
	SubClientName=TTBC.SUBCLIENT_NAME
	from(
	select SUBCLIENT_CODE,SUBCLIENT_NAME from T_TMS_Batch_Client
	) as TTBC where T_CSC_SubClient.SubClientCode=TTBC.SUBCLIENT_CODE and SourceSystemId=@SourceSystemId

	
	insert into T_CSC_SubClient(
		ClientId,
		SourceSystemId,
		SubClientCode,
		SubClientName,
		CreatedAt,
		CreatedBy,
		UpdatedAt,
		UpdatedBy
	)select
		null,
		@SourceSystemId,
		TTBC.SUBCLIENT_CODE,
		TTBC.SUBCLIENT_NAME,
		getutcdate(),
		@User,
		getutcdate(),
		@User
		from T_TMS_Batch_Client TTBC where TTBC.SUBCLIENT_CODE not in
		(select distinct SubClientCode from T_CSC_SubClient a where a.SourceSystemId=@SourceSystemId)
end
go

这里其中通过取其中一个表的一列或者几列，不用游标，纯属的原生sql语句。

转载于:https://www.cnblogs.com/qianlovebeijixiong/p/3240379.html