我的T

我的T-SQL语法有什么问题？(What is wrong with my T-SQL syntax?)

当我尝试运行此语句时，我不断收到以下错误消息。 我在SSIS中的OLE DB命令中运行它。

'）附近的语法不正确

显然这是一个易于阅读的错误消息，但我不知道为什么我会得到它。 我检查了T-SQL文档，我很确定我需要它们的括号。

INSERT INTO dbo.Table1 ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] ) SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.Table2 WHERE NOT ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] IN ( SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.table1 ) )

I keep getting the following error message when I try to run this statement. I'm running it inside of an OLE DB command in SSIS.

Incorrect syntax near ')'

Obviously this is an easy to read error message but I don't know why I'm getting it. I've checked the T-SQL documentation and I'm pretty sure I have parenthesis where they are needed.

INSERT INTO dbo.Table1 ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] ) SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.Table2 WHERE NOT ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] IN ( SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.table1 ) )

最满意答案

更新

马丁史密斯对M.Ali的答案写了一篇很好的评论 - 你确定你在这里做对了吗？ 如果任何列中的任何值在两个表中都不同，则select语句将返回它。 这意味着如果表2中的记录具有所有相同的值，除了表1中具有记录的记录，则select语句将返回它，并且您可能会得到最坏的主键违规错误 - 重复的行（除了表1中的那一列）。 考虑到你应该做的是从表2中选择表1中不存在键列的所有记录 - 任何唯一约束或索引的一部分，而不仅仅是主键。

因此，假设ID是主键，并且为了演示，您在Phone和Email上有一个唯一索引，您需要的是这样的：

INSERT INTO dbo.Table1 ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] ) SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.Table2 t2 WHERE NOT EXISTS ( SELECT 1 FROM Table1 t1 WHERE t1.ID = t2.ID OR (t1.Phone = t2.Phone AND t1.Email = t2.Email) )

第一个版本

IN运算符不像SQL Server中那样工作。 它左侧只能有一个操作数，权限方面有许多操作数，因此它基本上将单个值与列表进行比较。 T-SQL IN运算符的工作方式如下：

Col IN(value1, value2....valuen)

相当于

Col = value1 or Col = value2....or Col = valuen`

它在标准SQL中有效，并且有支持它的数据库，如MySql，但SQL Server没有。

您正在寻找EXCEPT ：

INSERT INTO dbo.Table1 ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] ) SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.Table2 EXCEPT SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.table1

Update

Martin Smith wrote a great comment on M.Ali's answer - Are you sure you are doing the right thing here? If any of the values in any of the columns is different in both tables, the select statement will return it. This means that if you have a record in table 2 that has the all the same values except one with a record in table 1, the select statement will return it and you might get either a primary key violation error of worst - duplicated rows (except that single column) in you table 1. Taking that into consideration what you should probably do is select all the records from table 2 where the key columns does not exists in table 1 - any column that is a part of any unique constraint or index, not just the primary key.

So, assuming ID is the primary key, and for the sake of the demonstration you have a unique index on Phone and Email, what you need is something like this:

INSERT INTO dbo.Table1 ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] ) SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.Table2 t2 WHERE NOT EXISTS ( SELECT 1 FROM Table1 t1 WHERE t1.ID = t2.ID OR (t1.Phone = t2.Phone AND t1.Email = t2.Email) )

First version

The IN operator doesn't work like that in SQL Server. It can have only one operand on it's left side, and many operands on it's rights side, so it basically compares a single value to a list. T-SQL IN operator works like this:

Col IN(value1, value2....valuen)

is equivalent to

Col = value1 or Col = value2....or Col = valuen`

It is valid in standard SQL and there are databases that supports it such as MySql, but SQL Server doesn't.

You are looking for EXCEPT:

INSERT INTO dbo.Table1 ( [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] ) SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.Table2 EXCEPT SELECT [ID], [Supplier], [Level], [Status], [Core], [Location], [Outsourced], [Contact], [Phone], [Email] FROM dbo.table1