SQL Server Performance

if not insert, update

Discussion in 'SQL Server DTS-Related Questions' started by boutwater, Oct 27, 2003.

  1. boutwater Member

    I am using dts to move data from my production db to a history db. I have it working right now where it will append new data, but I can't update old data. For example, I have an entry with a primary key on cartonID. That same carton changes some of it's values quite often. Once this carton is in my history db for the first time, I can't use DTS to move it over again. It gives me an error saying that that cartonID already exists. Is there any way I can tell DTS something like this: If the carton is already in the history db and the insert fails, then do an update ? Thanks for all your help,
    Ben
  2. ykchakri New Member

    Hi,

    I don't think it's possible to do this with DTS. You can either replace or append data using DTS.
  3. Jon M Member

    You can use the "Execute SQL" task. After executing the Insert statement, get the error number by using @@error. If @@error <> 0 then the cartonID is already existing then execute the necessary Update.

    Jon M
  4. boutwater Member

    How do you update with DTS? Thanks for your help,
    Ben
  5. boutwater Member

    Responding to vkchakri comment about replacing. Can I replace a single row in a table, or does that mean replacing the whole table? Thanks,
    Ben
  6. ykchakri New Member

    I was talking about the option 'Replace Existing Data' under copy tab. This will replace the entire data in the table.
  7. boutwater Member

    Someone told me that I might be able to do this with a stored procedure and a trigger. I'm not familiar with either of these. Is this something that would be easy to do, and schedulable? Thanks,
    Ben
  8. ykchakri New Member

    If you are planning to do it by means of a stored procedure, you can use a simple 'If exists' to check if the cartonID already exists, do an update if exists, if not do an insert. I don't think you even need a trigger.
  9. boutwater Member

    To schedule a stored procedure, I would just tell it to run every so often from my application. Is that correct, or is there a way to schedule stored procedures within SQL Server? Also, would it be advantages to run a stored procedure from my program, or to just do the SQL statements from my program? Finally, how do I set up a new stored procedure? Do I just save it as *.sql and call it from my program? Thanks for all your help,
    Ben
  10. satya Moderator

    Using JOB SCHEDULER (in EM management ---> SQLAgent ---> Jobs) you can schedule the jobs by using the SP.

    Refer to books online for CREATE PROCEDURE topic for more information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. boutwater Member

    Also, if I want to select from one db, and insert into another, how do I distinguish, in my query, that the select is associated with the 1st db, and the insert is associated with the 2nd? Thanks,
    Ben
  12. ykchakri New Member

    You have to qualify the table name with the DB name. For example DB1..Table1

Share This Page