MS SQL Server equivalent query

I have this query I use on MySql, and I'm trying to translate that to make it work on MS SQL Server.

INSERT INTO totable (col1,col2,col3)

(SELECT col1,col2,col3 FROM fromtable AS x)

ON DUPLICATE KEY UPDATE col1=x.col1,col2=x.col2,col3=x.col3;

Basically it inserts all rows from one table into another, and if you get a unique key constraint, it updates that rows instead. So far I haven't found any equivalent for MS SQL Server Anyone have a suggestion?

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

WesleyB wrote:

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Well...I don't have that amount of time Anyway...to elaborate a little bit...I have a working solution, but I'm trying to make it better. Currently this is done from a c++ program in a cursor loop. I send a select, insert and update statement to a function, selects a dataset and for each row in that dataset I'll first try to update, and if the update fails (returns no affected rows), I execute the insert statement. Needless to say that it isn't very efficient, but I can be flexible in terms of prepare temporary tables, send in help queries etc. I just can't figure out a better way than the current scenario...I just think that it really have to be a better way to do this.

|||

Rather than use a CURSOR, I would load the data in question into a staging table, using a table variable or #temp table, then with a single update statement, update all pertinent rows in the production table, a second query to delete those rows from the staging table, and then a third query to add the remainder to the production table.

Depending upon the number of rows, it is likely to be quite a bit more efficient and 'faster'.

|||

Why not doing first the update then the insert ?

Update SomeTable
SET
col1=x.col1
col2=x.col2,
col3=x.col3
From SomeTable
INNER JOIN fromtable x
ON --Place your join conditions here

INSERT INTO totable (col1,col2,col3)

(
SELECT col1,col2,col3
FROM fromtable AS x
WHERE NOT EXISTS
(
Select * from totable T
Where t.SomeColumn = x.SomeColumn --These should be your join conditions
)
)

Jens K. Suessmeyer.

http://www.sqlserver2005.de