Post reply

Insert into...select...on duplicate key update

  • November 13, 2011 at 7:46 am

    #146624

    I need to insert rows into the table from remote server with sql server 2005 running on it. If the row already exists it should be updated or overwritten, otherwise it should be inserted. I have written this query. But it doesn't work. The error is something like "wrong syntax near word "on"" (i have russian version, so I'm not quiet sure what the exact translation is).

    insert into "m29server-chequehead"

    select * from "192.200.101.131".ses.dbo.chequehead

    where dateoperation<getdate()

    and dateoperation>DATEADD(DAY,-1, CONVERT(CHAR(8), GETDATE(),112))

    --i need rows with date operation = today and yesterday

    on duplicate key update *="192.200.101.131".ses.dbo.chequehead.*

    What would you recomend to make this query work?

    There is field 'id', which is Primary Key.

  • November 13, 2011 at 7:54 am

    #1406830

    This is not sql server syntax.

    If you're on 2K5, I think you have to do it in 2 steps (3)

    Save results to temp table (optional)

    Update based on inner join,

    then insert where not exists

    Edit Confirmed, merged was introduced on 2K8. So if you're on 2K5 on both instances you have no option to do it in 2 runs.

    Maybe there's a way to run it locally, but that should be slow as hell.

  • November 13, 2011 at 8:05 am

    #1406835

    can you write aproximately syntax of 2 last steps? why do i have to do update based on inner join? it seems to me, i can sip it.

  • November 13, 2011 at 8:43 am

    #1406841

    UPDATE Base SET Col = Src.Col...

    FROM from "192.200.101.131".ses.dbo.chequehead Src

    INNER JOIN dbo.LocalTable Base

    ON Src.id = Base.Id

    WHERE Src.dateoperation >= DATEADD(D, 0, DATEDIFF(D, 1, GETDATE())) --yesterday midnight

    -- This is useless unless you have data from the future in there

    -- AND Src.dateoperation =< Getdate()

    Then insert where not exists.

    You can't do an insert & an update in the same step in sql 2K5. Those have to be separated. In 2K8+ you have the merge operation that does that.

  • November 13, 2011 at 3:38 pm

    #1406879

    Thanks a lot!

  • November 14, 2011 at 5:01 pm

    #1407587

    Another problem. I've made this scripts work. They copy and update when you hit 'F5' of 'Execute' in the editor. But when i create a job, running this script, i get error message "wrong syntax near "m29server-chequehead" and "wrong syntax near "192.200.101.131".ses.dbo.chequehead". I choose the same base. The type of the step is T-Sql.

  • November 14, 2011 at 7:11 pm

    #1407596

    You messed up somewhere in the copy / paste.

    You could always try to use [server name or ip] instead of using quotes. Maybe that's the difference here.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply