Why doesn't this specific syntax work for upserting?

Question

Hello,

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!

Answer 1

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.

Answer 2

According to Books Online the update command requires the set keyword, and it must come before the optional from keyword. The insert command doesn't have a stand alone from keyword, the from only exists as part of a select statement 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. For insert if the source and destination have the same struture then you can use insert into TARGTET_TABLE_NAME select * from SOURCE_TABLE_NAME BUT 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.