根据if条件插入相同的临时表 [英] Insert into same temp table based on if condition

问题描述

>大家好，下面是一个if语句，我想在一个名为#t的表中填充表结果，其中if语句为真。

我该怎么做？以下似乎不起作用：

>Hi there, below is an if statement in which I want to populate the table result within a table called #t for which ever statement is true.
How do I do this? The below does not seem to work:

SET @OpenQuerySql = 'IF  (' + cast(@FirstTableMonth as varchar) + '+99) = CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)
BEGIN
		 SELECT *
		FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' ) 
UNION ALL 
	*
into #t					
FROM #temp
END
else 
begin 
SELECT ACCT_NO,
		*
		FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' ) 
into #t	
UNION ALL 
SELECT ACCT_NO,
		*
		FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL2 + ''' ) 
end
'

推荐答案

您的代码存在一些问题，但找到它们的最简单方法是使用 PRINT @OpenQuerySql 代替 EXEC - 通过这种方式，您可以使用SSMS中的内置错误报告来帮助您找到错误。



在这种情况下，您在第一个 UNION ALL 之前错过了 SELECT 而且你不是汉将插入正确地插入#t 以获取 UNION 查询。



试试这个

There are several issues with your code but the easiest way to find them is to put a PRINT @OpenQuerySql in place of the EXEC - that way you can use the built-in error reporting in SSMS to help you locate the errors.

In this case, you have missed a SELECT before the first UNION ALL and you are not handling the insert into #t correctly for a UNION query.

Try this

SET @OpenQuerySql = 'IF  (' + cast(@FirstTableMonth as varchar) + '+99) = CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)
BEGIN
   SELECT * INTO #t FROM 
   (
	SELECT *
	FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' ) 
	UNION ALL 
	SELECT * FROM #temp
) as tmp
END
else 
begin 
	SELECT * INTO #t FROM 
	(
	
		SELECT ACCT_NO, *
		FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' ) 
		UNION ALL 
		SELECT ACCT_NO, *
		FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL2 + ''' ) 
	) as tmp
end
'

这篇关于根据if条件插入相同的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！