SQL Server Performance Forum – Threads Archive
if not insert, update
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
Hi, I don’t think it’s possible to do this with DTS. You can either replace or append data using DTS.
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
How do you update with DTS? Thanks for your help,
Ben
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
I was talking about the option ‘Replace Existing Data’ under copy tab. This will replace the entire data in the table.
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
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.
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
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
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
You have to qualify the table name with the DB name. For example DB1..Table1
]]>