比较SQL Server中两个表之间的数据 [英] Comparing data between two tables in SQL Server

问题描述

我有一个sp，它将两张桌子一起比较。

一切看起来都很好但是当我尝试执行它时，它出来了

语法不正确在关键字FROM附近



这里我的sp声明：

I have a sp which shd compare two tables together.
Everything looks fine but when i try to exec it, it comes out with
Incorrect syntax near the keyword FROM

Here my sp Statement:

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']'  +
'EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM' + @Table_Name

EXECUTE sp_executesql @Dynamictb2

推荐答案

我运行你的sql和sw为 PRINT 添加 EXECUTE 并获得此

I ran your sql and swapped the EXECUTE for a PRINT and got this

SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROM [xyz]EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROMxyz

所以我想这会解决你的问题

so I think this will fix your problem

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']'  +
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name

注意在EXCEPT之前和在第3和第5行之后的额外空格



编辑 - 来自OP评论我们现在知道这是与在一个由

Note the extra spaces before EXCEPT and after FROM on the 3rd and 5th lines respectively

EDIT - from OP comment we now know that this is within an SP called by

EXEC spDynamic1 '[dbo].[Per201102]'

调用的SP中，所以 SET



试试这个：



so there are extra square brackets being inserted by the SET

Try this instead:

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name + 
' EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name

EDIT2 - 响应OP评论 - 我认为SP需要改变类似于此（注意 - 未经测试）

EDIT2 - in response to OP comment - I think the SP needs to change similar to this (note - untested)

ALTER PROC spDynamic1
 (
 @Table_Name sysname ,@Table_Name2 sysname
 )
 AS
 BEGIN
 SET NOCOUNT ON;
 DECLARE @Dynamictb2 nvarchar(255)

 SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
 FROM [' + @Table_Name + ']' +
 ' EXCEPT
 SELECT DISTINCT [Projektdefinition DB] AS Zugänge
 FROM ' + @Table_Name2

 EXECUTE sp_executesql @Dynamictb2

 END
 GO 

这篇关于比较SQL Server中两个表之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！