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)
AS
BEGIN
	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,@StuField,@StuType,@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, 1, LEN(@StuSql) - 1)
	END
--tc.f1,tc.f2,tc.f4,tc.f5,tc.f6
--sname,gender,mobile,address,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 + ''', 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 + @valueSql

					EXEC sp_executesql @sql
				END
			ELSE IF @type = 1  -- 二类学生
				BEGIN
					SET @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 + @valueSql

					EXEC sp_executesql @sql
				END
	END
END

2、优化后

ALTER PROCEDURE [dbo].[pro_demo] 
@type varchar(10),
@stuId varchar(10),
@stuNo VARCHAR(20),
@tFlag varchar(1),
@stuClassIds varchar(max)
AS
BEGIN
SET NOCOUNT ON;
-- 定义游标，编辑sql, 取得字段对应关系
DECLARE @TempStuSql VARCHAR(2000)
DECLARE @StuSql VARCHAR(2000)
--create cursor
DECLARE stuCursor CURSOR FOR
SELECT TempCustomerField,CustomerField,CustomerType,IsOptionItem FROM UpgradeStuFieldDefine
WHERE ActType = @type 
AND ActId = @stuId 
AND LOWER(TempCustomerField) != 'id' 
AND LOWER(CustomerField) != 'code' 
ORDER BY ID DESC
--open cursor
OPEN stuCursor
DECLARE @TempCustomerField VARCHAR(5000)
DECLARE @CustomerField VARCHAR(100)
DECLARE @CustomerType VARCHAR(1)
DECLARE @IsOptionItem VARCHAR(1)
SET @TempStuSql = ''
SET @StuSql = ''
--fetch next from cursor
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
WHILE @@FETCH_STATUS = 0
BEGIN 	
SET @TempStuSql = 'tc.' +@TempCustomerField + ',' + @TempStuSql 
SET @StuSql = @CustomerField + ',' + @StuSql 
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
END
--print '第1个打印'
--print @TempStuSql --tc.f1,tc.f2,tc.f4,tc.f5,tc.f6,
--print @StuSql     --customername,gender,mobile,address,email,
--close and deallocate cursor
CLOSE stuCursor

--@TempStuSql and @StuSql
IF LEN(@TempStuSql) > 1
BEGIN
SET @TempStuSql = SUBSTRING(@TempStuSql, 1, LEN(@TempStuSql) -1)
SET @StuSql = SUBSTRING(@StuSql, 1, LEN(@StuSql) - 1)
END
--@customerTable
DECLARE @customerTable NVARCHAR(20)
IF @CustomerType = 1
SET @customerTable = 'Astudent'
ELSE 
SET @customerTable = 'Bstudent'

IF LEN(@TempStuSql) > 1
--main code #s
BEGIN
OPEN stuCursor
DECLARE @sql NVARCHAR(max)	     --last sql
DECLARE @insertSql NVARCHAR(max) --insert part
DECLARE @valueSql NVARCHAR(max)  --value part	
DECLARE @valueSql1 VARCHAR(2000) --START
DECLARE @valueSql2 VARCHAR(2000) --DYNAMIC COLUMN
DECLARE @valueSql3 VARCHAR(2000) --FROM
DECLARE @valueSql4 VARCHAR(2000) --LEFT JOIN
DECLARE @valueSql5 VARCHAR(2000) --END
SET @valueSql2=''
SET @valueSql4=''
IF @type = 0 -- Ma学生
BEGIN
SET @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,@IsOptionItem
WHILE @@FETCH_STATUS = 0
BEGIN 	
IF @IsOptionItem='1'
BEGIN
SET @valueSql2=','+@TempCustomerField+'.id'+@valueSql2
SET @valueSql4=@valueSql4+' left join OptionItem '+@TempCustomerField+' on tc.'+@TempCustomerField+'='+@TempCustomerField+'.itemName '
END
ELSE
SET @valueSql2=',tc.'+@TempCustomerField+@valueSql2
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
END
CLOSE stuCursor
DEALLOCATE stuCursor		
--print '打印left join=='
--print @valueSql4
END
ELSE IF @type = 1  -- Survey学生
BEGIN
SET @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,@IsOptionItem
WHILE @@FETCH_STATUS = 0
BEGIN 	
IF @IsOptionItem='1'
BEGIN
SET @valueSql2=','+@TempCustomerField+'.id'+@valueSql2
SET @valueSql4=@valueSql4+' left join OptionItem '+@TempCustomerField+' on tc.'+@TempCustomerField+'='+@TempCustomerField+'.itemName '
END
ELSE
SET @valueSql2=',tc.'+@TempCustomerField+@valueSql2
FETCH NEXT FROM stuCursor INTO @TempCustomerField,@CustomerField,@CustomerType,@IsOptionItem
END
CLOSE stuCursor
DEALLOCATE stuCursor	
END
IF LEN(@valueSql4)>1
SET @valueSql=@valueSql1+@valueSql2+@valueSql3+@valueSql4+@valueSql5 --@valueSql
ELSE
SET @valueSql=@valueSql1+@valueSql2+@valueSql3+@valueSql5
IF ISNULL(@tFlag, 1) = 0 --页面选择了学生id，直接升级选择的学生
SET @valueSql = @valueSql + 'AND tc.id IN (' + REPLACE(@stuClassIds,'@', ',') + ') ' --@valueSql
SET @sql = @insertSql + @valueSql
--print '打印'
--print @sql
EXEC sp_executesql @sql
END
--main code #e
END