如何在插入查询中设置局部变量的值 [英] How to set value of Local variable in Insert Query

问题描述

在执行插入时，我正在执行合并语句中的插入和更新，我想插入PRODCODE值，这样就不应重复该值.

I am doing Insert and update in merge statement when Insert in executed i want to insert the PRODCODE value such a way that it should not be repeated.

DECLARE @vProductCode AS VARCHAR(30);
SET @vProductCode = 'P0000000001';
MERGE mergeSupplierCatalogue AS SC
USING ( SELECT	[PRODCODE],[SUPPLRID],[CLTGCODE],[PRODTCDE]
		FROM tmpSupplierCatalogue) AS tmpSC ON (SC.[PRODTCDE] = tmpSC.[PRODTCDE])
WHEN NOT MATCHED THEN
		INSERT 
		([PRODCODE],[SUPPLRID],[CLTGCODE],[PRODTCDE])
		VALUES
		('P' + RIGHT('0000000000' + CAST((SUBSTRING(@vProductCode,2,12)+1)AS VARCHAR),10),tmpSC.[SUPPLRID],tmpSC.[CLTGCODE],tmpSC.[PRODTCDE])
WHEN MATCHED THEN
		UPDATE SET
		SC.[PRODCODE]  = tmpSC.[PRODCODE],
		SC.[SUPPLRID]  = tmpSC.[SUPPLRID],
		SC.[CLTGCODE]  = tmpSC.[CLTGCODE],
		SC.[PRODTCDE]  = tmpSC.[PRODTCDE];

//执行合并语句后，我得到的结果为

//after executing the merge statement i am getting the result as

PRODCODE	SUPPLRID	CLTGCODE    PRODTCDE
------------------------------------------------------
P0000000002	SUPP0000001	CW002	   59376	
P0000000002	SUPP0000001	CW002	   59512	
P0000000002	SUPP0000001	CW002	   601602

//在哪里我希望结果集为

//Where as i want result set to be

PRODCODE	SUPPLRID	CLTGCODE    PRODTCDE
------------------------------------------------------
P0000000001	SUPP0000001	CW002	     59376	
P0000000002	SUPP0000001	CW002	     59512	
P0000000003	SUPP0000001	CW002        601602

我怎么做?有什么建议?是否可以在插入查询中设置vProductCode的值?

How do i do that? Any Suggestions? is it possible to set the value of vProductCode in Insert query? Thanks in advance!

推荐答案

"SQL Server错误消息-消息141-为变量分配值的SELECT语句不得与数据检索操作结合使用. "

因此，您无法递增"@vProductCode"并同时检索数据.
但是，

"SQL Server Error Messages - Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

So you cannot increment your "@vProductCode" and retrieve data at the same time.
However,

DECLARE @vProductCode AS INT;
SET @vProductCode = 0; -- Chage this to set an offset for autoincrementing [NEWCODE]
MERGE mergeSupplierCatalogue AS SC
USING ( SELECT	[PRODCODE],[SUPPLRID],[CLTGCODE],[PRODTCDE],
	
                'P'+ RIGHT('0000000000'+CAST(ROW_NUMBER() OVER (ORDER BY [PRODCODE] + @vProductCode)AS VARCHAR),10) AS [NEWCODE]

                FROM tmpSupplierCatalogue) AS tmpSC ON (SC.[PRODTCDE] = tmpSC.[PRODTCDE])
WHEN NOT MATCHED THEN
		INSERT 
		([PRODCODE],[SUPPLRID],[CLTGCODE],[PRODTCDE])
		VALUES
		(tmpSC.[NEWCODE],tmpSC.[SUPPLRID],tmpSC.[CLTGCODE],tmpSC.[PRODTCDE])
WHEN MATCHED THEN
		UPDATE SET
		SC.[PRODCODE]  = tmpSC.[PRODCODE],
		SC.[SUPPLRID]  = tmpSC.[SUPPLRID],
		SC.[CLTGCODE]  = tmpSC.[CLTGCODE],
		SC.[PRODTCDE]  = tmpSC.[PRODTCDE];

将为您提供可以管理的结果.

will give you a result you can manage.

这篇关于如何在插入查询中设置局部变量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！