`
Franciswmf
  • 浏览: 805287 次
  • 性别: Icon_minigender_1
  • 来自: 上海
文章分类
社区版块
存档分类
最新评论

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
分享到：
| Struts2解析参数与普通方法解析参数的不同
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    经典SQL语句大全

    - 这些函数涵盖了数据操作的各个方面，从字符串处理到日期时间计算，再到数据类型转换等。 这些知识点覆盖了SQL Server 中常用的函数和操作，对于数据开发者来说非常实用。掌握这些知识点能够帮助开发者更加高效地...

    IP地址库20181219（SQL Server）

    这个IP地址库是针对SQL Server设计的，意味着它可以方便地导入到SQL Server环境中，利用SQL Server的强大查询能力和高效的数据管理功能，进行快速、精准的IP地址查询和分析。 6. **数据库操作与应用** 在实际使用...

    用友U8_EAI数据接口常见问题.doc

    - 将查询得到的时间戳复制到适配器配置中相应的字段，确保后续导入的数据是在该时间戳之后修改的。 - **注意事项**：此方法仅适用于客户、产品等基础数据的同步，对于各类单据目前不支持时间戳同步。 #### 2. U8...

    数据库删除完全重复和部分关键字段重复的记录

    本文将详细介绍如何处理两种类型的重复记录：完全重复和部分关键字段重复的记录，并提供在MySQL、SQL Server和Oracle数据库环境下对应的解决方案。 **完全重复记录的处理** 完全重复记录指的是所有字段值都相同的...

    2021-2022计算机二级等级考试试题及答案No.3826.docx

    - **知识点**：在编程语言中，函数或过程调用时，实参（实际参数）和形参（形式参数）之间的对应关系非常重要。虽然变量名可以不同，但必须确保参数的数量和类型一致，以便正确传递数据。 #### 3. MySQL 服务器类型...

Magicbox
Global site tag (gtag.js) - Google Analytics