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

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)ASBEGINSET NOCOUNT ON;-- 定义游标，编辑sql, 取得字段对应关系DECLARE @TempStuSql VARCHAR(2000)DECLARE @StuSql VARCHAR(2000)--创建游标DECLARE stuCursor CURSOR FORSELECT TempStuField,StuField,StuType,isSchoolItem FROM UpgradeStuFieldDefineWHERE xType = @type AND xId = @stuId AND LOWER(TempStuField) != 'id' AND LOWER(StuField) != 'code' ORDER BY ID DESC  --开启游标OPEN stuCursorDECLARE @TempStuField VARCHAR(5000)DECLARE @StuField VARCHAR(100)DECLARE @StuType VARCHAR(1)DECLARE @isSchoolItem VARCHAR(1)SET @TempStuSql = ''SET @StuSql = ''--fetch row by rowFETCH NEXT FROM stuCursor INTO @TempStuField,@StuField,@StuType,@isSchoolItem  --eg: f2 gender 0WHILE @@FETCH_STATUS = 0BEGIN IF @isSchoolItem='0'SET @TempStuSql = '(select o.id from OptionItem o where o.itemName=tc.' +@TempStuField + ') as ' +@TempStuField + ',' + @TempStuSql ELSESET @TempStuSql = 'tc.' +@TempStuField + ',' + @TempStuSql SET @StuSql = @StuField + ',' + @StuSql FETCH NEXT FROM stuCursor INTO @TempStuField,@StuField,@StuType,@isSchoolItemEND--@TempStuField @StuField @StuType 每个变量每次只能保存一行数据对应的一条数据--print @TempStuSql--print @StuSql--tc.f1,tc.f2,tc.f4,tc.f5,tc.f6,--sname,gender,mobile,address,email,CLOSE stuCursorDEALLOCATE stuCursor--释放游标IF LEN(@TempStuSql) > 1BEGINSET @TempStuSql = SUBSTRING(@TempStuSql, 1, LEN(@TempStuSql) -1)SET @StuSql = SUBSTRING(@StuSql, 1, LEN(@StuSql) - 1)END--tc.f1,tc.f2,tc.f4,tc.f5,tc.f6--sname,gender,mobile,address,emailDECLARE @stuTable NVARCHAR(20) -- IF @StuType = 1SET @stuTable = 'Astudent'ELSE SET @stuTable = 'Bstudent'IF LEN(@TempStuSql) > 1BEGIN-- 定义执行sqlDECLARE @insertSql NVARCHAR(max)DECLARE @valueSql NVARCHAR(max)DECLARE @sql NVARCHAR(max)IF @type = 0 -- 一类学生BEGINSET @insertSql = 'INSERT INTO ' + @stuTable + ' (id,Code,createTime,stuRefId,aStuType,aStuId,status,' + @StuSql + ') 'SET @valueSql = 'SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1, ' + @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 + @valueSqlEXEC sp_executesql @sqlENDELSE IF @type = 1  -- 二类学生BEGINSET @insertSql = 'INSERT INTO ' + @stuTable + ' (ID,Code,createTime,stuRefId,aStuType,aStuId,status,' + @StuSql + ') 'SET @valueSql = 'SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1, ' + @TempStuSql + ' FROM t2_' + @stuId + ' tc WHERE (SYS_BatchId = ''' + 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 + @valueSqlEXEC sp_executesql @sqlENDENDEND

2、优化后

ALTER PROCEDURE [dbo].[pro_demo] @type varchar(10),@stuId varchar(10),@stuNo VARCHAR(20),@tFlag varchar(1),@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,@CustomerField,@CustomerType,@IsOptionItemEND--print '第1个打印'--print @TempStuSql --tc.f1,tc.f2,tc.f4,tc.f5,tc.f6,--print @StuSql     --customername,gender,mobile,address,email,--close and deallocate cursorCLOSE stuCursor--@TempStuSql and @StuSqlIF LEN(@TempStuSql) > 1BEGINSET @TempStuSql = SUBSTRING(@TempStuSql, 1, LEN(@TempStuSql) -1)SET @StuSql = SUBSTRING(@StuSql, 1, 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 partDECLARE @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,Code,createTime,upgradeCustomerRefId,upgradeActType,upgradeActId,status,' + @StuSql + ') 'SET @valueSql1='SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 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, '') + ''' OR ''' + 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,@CustomerField,@CustomerType,@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,@CustomerField,@CustomerType,@IsOptionItemENDCLOSE stuCursorDEALLOCATE stuCursor--print '打印left join=='--print @valueSql4ENDELSE IF @type = 1  -- Survey学生BEGINSET @insertSql = 'INSERT INTO ' + @customerTable + ' (ID,Code,createTime,upgradeCustomerRefId,upgradeActType,upgradeActId,status,' + @StuSql + ') 'SET @valueSql1='SELECT REPLACE(NEWID(),''-'',''''),'''',GETDATE(),tc.id,''' + @type + ''',''' + @stuId + ''', 1'SET @valueSql3=' FROM T1_' + @stuId + ' tc 'SET @valueSql5='WHERE (SYS_BatchId = ''' + Isnull(@stuNo, '') + ''' OR ''' + 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,@CustomerField,@CustomerType,@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,@CustomerField,@CustomerType,@IsOptionItemENDCLOSE stuCursorDEALLOCATE stuCursorENDIF 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