sqlserver存储过程_根据表1表2字段对应关系将表1数据去重导入到表2

概述1、优化前 -- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- ============================================= 1、优化前

-- =============================================-- Author:		<Author,name>-- Create date: <Create Date,>-- Description:	<Description,>-- =============================================ALTER PROCEDURE [dbo].[pro_demo] 	@type varchar(10),@stuID varchar(10),@stuNo VARCHAR(20),@tFlag varchar(1),@stuClassIDs varchar(max)ASBEGIN	SET NOCOUNT ON;	-- 定义游标，编辑sql,取得字段对应关系	DECLARE @TempStusql VARCHAR(2000)	DECLARE @Stusql VARCHAR(2000)	--创建游标	DECLARE stuCursor CURSOR FOR		SELECT TempStuFIEld,StuFIEld,StuType,isSchoolitem FROM UpgradeStuFIElddefine		WHERE xType = @type 		AND xID = @stuID 		AND LOWER(TempStuFIEld) != 'ID' 		AND LOWER(StuFIEld) != 'code' 		ORDER BY ID DESC  --开启游标	OPEN stuCursor	DECLARE @TempStuFIEld VARCHAR(5000)	DECLARE @StuFIEld VARCHAR(100)	DECLARE @StuType VARCHAR(1)	DECLARE @isSchoolitem VARCHAR(1)		SET @TempStusql = ''	SET @Stusql = ''--fetch row by row	FETCH NEXT FROM stuCursor INTO @TempStuFIEld,@StuFIEld,@StuType,@isSchoolitem  --eg: f2 gender 0	WHILE @@FETCH_STATUS = 0		BEGIN 			IF @isSchoolitem='0'				SET @TempStusql = '(select o.ID from OptionItem o where o.itemname=tc.' +@TempStuFIEld + ') as ' +@TempStuFIEld + ',' + @TempStusql 			ELSE					SET @TempStusql = 'tc.' +@TempStuFIEld + ',' + @TempStusql 			SET @Stusql = @StuFIEld + ',' + @Stusql 			FETCH NEXT FROM stuCursor INTO @TempStuFIEld,@isSchoolitem		END--@TempStuFIEld @StuFIEld @StuType 每个变量每次只能保存一行数据对应的一条数据--print @TempStusql--print @Stusql--tc.f1,tc.f2,tc.f4,tc.f5,tc.f6,--sname,gender,mobile,address,email,CLOSE stuCursor	DEALLOCATE stuCursor	--释放游标	IF LEN(@TempStusql) > 1	BEGIN		SET @TempStusql = SUBSTRING(@TempStusql,1,LEN(@TempStusql) -1)		SET @Stusql = SUBSTRING(@Stusql,LEN(@Stusql) - 1)	END--tc.f1,tc.f6--sname,email	DECLARE @stutable NVARCHAR(20) -- 	IF @StuType = 1		SET @stutable = 'Astudent'	ELSE 		SET @stutable = 'Bstudent'			IF LEN(@TempStusql) > 1	BEGIN		-- 定义执行sql		DECLARE @insertsql NVARCHAR(max)		DECLARE @valuesql NVARCHAR(max)			DECLARE @sql NVARCHAR(max)				IF @type = 0 -- 一类学生				BEGIN					SET @insertsql = 'INSERT INTO ' + @stutable + ' (ID,Code,createTime,stuRefID,aStuType,aStuID,status,' + @Stusql + ') '					SET @valuesql = 'SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.ID,''' + @type + ''',''' + @stuID + ''',' + @TempStusql + ' FROM TempCustomer tc 						left JOIN Leads l ON l.TmpCustomerID = tc.ID 						WHERE l.ActivityID = ' + @stuID + ' AND (tc.BatchNo = ''' + Isnull(@stuNo,'') + ''' OR ''' + Isnull(@stuNo,'') + ''' = '''')						AND not exists (							SELECT 1 FROM t1 WHERE stuRefID = tc.ID AND aStuType = ' + @type + ' AND aStuID = ' + @stuID + '						) AND not exists (							SELECT 1 FROM t2 WHERE stuRefID = tc.ID AND aStuType = ' + @type + ' AND aStuID = ' + @stuID + '						) '					IF ISNulL(@tFlag,1) = 0 --页面选择了学生ID，直接升级选择的学生						SET @valuesql = @valuesql + 'AND tc.ID IN (' + REPLACE(@stuClassIDs,'@',',') + ') '					SET @sql = @insertsql + @valuesql					EXEC sp_executesql @sql				END			ELSE IF @type = 1  -- 二类学生				BEGIN					SET @insertsql = 'INSERT INTO ' + @stutable + ' (ID,' + @TempStusql + ' 						FROM t2_' + @stuID + ' tc WHERE (SYS_BatchID = ''' + Isnull(@stuNo,'') + ''' = '''') 						AND not exists (							SELECT 1 FROM t1 WHERE stuRefID = tc.ID AND aStuType = ' + @type + ' AND aStuID = ' + @stuID + '						) AND not exists (							SELECT 1 FROM t2 WHERE stuRefID = tc.ID AND aStuType = ' + @type + ' AND aStuID = ' + @stuID + '						) '					IF ISNulL(@tFlag,') + ') '										SET @sql = @insertsql + @valuesql					EXEC sp_executesql @sql				END	ENDEND

2、优化后

ALTER PROCEDURE [dbo].[pro_demo] @type varchar(10),@stuClassIDs varchar(max)ASBEGINSET NOCOUNT ON;-- 定义游标，编辑sql,取得字段对应关系DECLARE @TempStusql VARCHAR(2000)DECLARE @Stusql VARCHAR(2000)--create cursorDECLARE stuCursor CURSOR FORSELECT TempCustomerFIEld,CustomerFIEld,CustomerType,IsOptionItem FROM UpgradeStuFIElddefineWHERE ActType = @type AND ActID = @stuID AND LOWER(TempCustomerFIEld) != 'ID' AND LOWER(CustomerFIEld) != 'code' ORDER BY ID DESC--open cursorOPEN stuCursorDECLARE @TempCustomerFIEld VARCHAR(5000)DECLARE @CustomerFIEld VARCHAR(100)DECLARE @CustomerType VARCHAR(1)DECLARE @IsOptionItem VARCHAR(1)SET @TempStusql = ''SET @Stusql = ''--fetch next from cursorFETCH NEXT FROM stuCursor INTO @TempCustomerFIEld,@CustomerFIEld,@CustomerType,@IsOptionItemWHILE @@FETCH_STATUS = 0BEGIN 	SET @TempStusql = 'tc.' +@TempCustomerFIEld + ',' + @TempStusql SET @Stusql = @CustomerFIEld + ',' + @Stusql FETCH NEXT FROM stuCursor INTO @TempCustomerFIEld,@IsOptionItemEND--print '第1个打印'--print @TempStusql --tc.f1,--print @Stusql     --customername,--close and deallocate cursorCLOSE stuCursor--@TempStusql and @StusqlIF LEN(@TempStusql) > 1BEGINSET @TempStusql = SUBSTRING(@TempStusql,LEN(@TempStusql) -1)SET @Stusql = SUBSTRING(@Stusql,LEN(@Stusql) - 1)END--@customertableDECLARE @customertable NVARCHAR(20)IF @CustomerType = 1SET @customertable = 'Astudent'ELSE SET @customertable = 'Bstudent'IF LEN(@TempStusql) > 1--main code #sBEGINOPEN stuCursorDECLARE @sql NVARCHAR(max)	     --last sqlDECLARE @insertsql NVARCHAR(max) --insert partDECLARE @valuesql NVARCHAR(max)  --value part	DECLARE @valuesql1 VARCHAR(2000) --STARTDECLARE @valuesql2 VARCHAR(2000) --DYNAMIC ColUMNDECLARE @valuesql3 VARCHAR(2000) --FROMDECLARE @valuesql4 VARCHAR(2000) --left JOINDECLARE @valuesql5 VARCHAR(2000) --ENDSET @valuesql2=''SET @valuesql4=''IF @type = 0 -- Ma学生BEGINSET @insertsql = 'INSERT INTO ' + @customertable + ' (ID,upgradeCustomerRefID,upgradeActType,upgradeActID,' + @Stusql + ') 'SET @valuesql1='SELECT REPLACE(NEWID(),1'SET @valuesql3=' FROM TempCustomer tc left JOIN Leads l ON l.TmpCustomerID = tc.ID 'SET @valuesql5='	WHERE l.ActivityID = ''' + @stuID + ''' AND (tc.BatchNo = ''' + Isnull(@stuNo,'') + ''' = '''')AND not exists ( SELECT 1 FROM customer WHERE upgradeCustomerRefID = tc.ID AND upgradeActType = ' + @type + ' AND upgradeActID = ' + @stuID + ') AND not exists ( SELECT 1 FROM company WHERE upgradeCustomerRefID = tc.ID AND upgradeActType = ' + @type + ' AND upgradeActID = ' + @stuID + ') 'FETCH NEXT FROM stuCursor INTO @TempCustomerFIEld,@IsOptionItemWHILE @@FETCH_STATUS = 0BEGIN 	IF @IsOptionItem='1'BEGINSET @valuesql2=','+@TempCustomerFIEld+'.ID'+@valuesql2SET @valuesql4=@valuesql4+' left join OptionItem '+@TempCustomerFIEld+' on tc.'+@TempCustomerFIEld+'='+@TempCustomerFIEld+'.itemname 'ENDELSESET @valuesql2=',tc.'+@TempCustomerFIEld+@valuesql2FETCH NEXT FROM stuCursor INTO @TempCustomerFIEld,@IsOptionItemENDCLOSE stuCursorDEALLOCATE stuCursor		--print '打印left join=='--print @valuesql4ENDELSE IF @type = 1  -- Survey学生BEGINSET @insertsql = 'INSERT INTO ' + @customertable + ' (ID,1'SET @valuesql3=' FROM T1_' + @stuID + ' tc 'SET @valuesql5='	WHERE (SYS_BatchID = ''' + Isnull(@stuNo,'') + ''' = '''') AND not exists ( SELECT 1 FROM T1 WHERE upgradeCustomerRefID = tc.ID AND upgradeActType = ' + @type + ' AND upgradeActID = ' + @stuID + ') AND not exists ( SELECT 1 FROM T2 WHERE upgradeCustomerRefID = tc.ID AND upgradeActType = ' + @type + ' AND upgradeActID = ' + @stuID + ') 'FETCH NEXT FROM stuCursor INTO @TempCustomerFIEld,@IsOptionItemENDCLOSE stuCursorDEALLOCATE stuCursor	ENDIF LEN(@valuesql4)>1SET @valuesql=@valuesql1+@valuesql2+@valuesql3+@valuesql4+@valuesql5 --@valuesqlELSESET @valuesql=@valuesql1+@valuesql2+@valuesql3+@valuesql5IF ISNulL(@tFlag,1) = 0 --页面选择了学生ID，直接升级选择的学生SET @valuesql = @valuesql + 'AND tc.ID IN (' + REPLACE(@stuClassIDs,') + ') ' --@valuesqlSET @sql = @insertsql + @valuesql--print '打印'--print @sqlEXEC sp_executesql @sqlEND--main code #eEND

总结

以上是内存溢出为你收集整理的sqlserver存储过程_根据表1表2字段对应关系将表1数据去重导入到表2全部内容，希望文章能够帮你解决sqlserver存储过程_根据表1表2字段对应关系将表1数据去重导入到表2所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。