为什么这个特定语法不适用于更新插入？
我正在使用 SQL Server 2005，我想同步两个具有相同定义但存在于不同数据库中的表。 MERGE INTO 仅存在于 2008 年，我更喜欢一种不必在 UPDATE 中指定列的语法。因此，我偶然发现使用以下语法的各种帖子：
UPDATE Destination FROM (Source INTERSECT Destination)
INSERT INTO Destination FROM (Source EXCEPT Destination)
但是当我尝试执行它，我得到：
Incorrect syntax near the keyword 'FROM'.
我怎样才能让它工作？我有多个需要同步的表，并且我不想在每个语句中指定所有列。
感谢您的任何提示！
I'm using SQL Server 2005 and I want to synchronize two tables which have the same definition but exist in different databases. MERGE INTO only exists in 2008 and I'd prefer a syntax where I don't have to specify columns in the UPDATE. So I stumbled upon various posts using the following syntax:
UPDATE Destination FROM (Source INTERSECT Destination)
INSERT INTO Destination FROM (Source EXCEPT Destination)
But when I try to execute it I get:
Incorrect syntax near the keyword 'FROM'.
How can I get this working? I have multiple tables which I need to synchronize and I don't want to specify all the columns in every statement.
Thanks for any hint!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据联机丛书，
更新 命令需要set关键字，并且它必须位于可选的from关键字之前。插入命令< /a> 没有独立的from关键字，from仅作为select语句的一部分存在，或者作为派生表源或在公共表表达式内。您引用的链接未显示有效的 SQL Server 2005 语法。
“我怎样才能让它工作？我有多个需要同步的表，并且我不想在每个语句中指定所有列。”
对于
更新，您必须指定所有列。对于insert，如果源和目标具有相同的结构，则可以使用insert into TARGTET_TABLE_NAME select * from SOURCE_TABLE_NAMEBUT，不建议用于生产代码中，如果源或目标发生更改，该语句就会中断。如果源和目标不同，则必须至少在插入的一侧指定列。According to Books Online the
updatecommand requires thesetkeyword, and it must come before the optionalfromkeyword. Theinsertcommand doesn't have a stand alonefromkeyword, thefromonly exists as part of aselectstatement either as a derived table source or within a common table expression.The link you reference is not showing valid SQL Server 2005 syntax.
"How can I get this working? I have multiple tables which I need to synchronize and I don't want to specify all the columns in every statement."
For
update, you must specify all the columns. Forinsertif the source and destination have the same struture then you can useinsert into TARGTET_TABLE_NAME select * from SOURCE_TABLE_NAMEBUT that is not recommended for production code, if the source or destination change, the statement would break. If source and destination differ, then you must specify columns on at least one side of the insert.如果这不能回答您的问题，我很抱歉，但假设这样做的全部原因是为了节省时间，您不能右键单击源表并生成 INSERT 脚本，然后右键单击目标表并生成一个空白的 SELECT 脚本，然后将两者结合起来？仅当您的环境可以接受“杀戮和填充”时，这才有效。
I'm sorry if this doesn't answer your question, but assuming the whole reason for this is in the interest of saving time, can't you just right-click the source table and generate the INSERT script, then right-click the destination table and generate a blank SELECT script, then combine the two? This will only work if a kill-and-fill is acceptable in your environment.