June 25, 2002 at 10:57 am
I am relatively new to the dba world. I am trying to write a statement to use in a dts package which will update a table every 15 min. with values from an identically structured table. The update follows the general format:
DECLARE @SSNum char(9)
SET @SSNum = 'SSnumpinVRU.SSNum'
UPDATE SSnumpin
SET SSNum = @SSNum
This method has worked for the other columns in the db but errors: Violation of PK_Constraint for the PK_COLUMN
Shouldn't it allow the PK to be updated? Is there an option to set similar to IDENTITY_INSERT ON? Or is there an altogether better method for this? Any help from you veterans would help alot.
June 25, 2002 at 12:55 pm
IF you have a duplicate PK, this will fail, regardless.
You can update the PK of a table as long as it does not conflict.
Can you post all the code?
Steve Jones
June 25, 2002 at 1:10 pm
thanks for replying steve. I must have had brainlock this morning. I just need to update the columns in each record with matching primary keys, not update the keys. However, I'm not sure what I'm doing is the best solution. I am taking a flat text file and populating a table with an identical structure to the main table, and then querying the "temp" table to update the main table. A sample:
DECLARE @PIN char(6)
SET @PIN = 'SSnumpinVRU.PIN'
DECLARE @FirstTimeFlag char(1)
SET @FirstTimeFlag = 'SSnumpinVRU.FirstTimeFlag'
DECLARE @CallCounter char(5)
SET @CallCounter = 'SSnumpinVRU.CallCounter'
DECLARE @LastName char(20)
SET @LastName = 'SSnumpinVRU.LastName'
DECLARE @FirstName_MiddleInitial char(25)
SET @FirstName_MiddleInitial = 'SSnumpinVRU.FirstName_MiddleInitial'
DECLARE @EmpNum char(3)
SET @EmpNum = 'SSnumpinVRU.EmpNum'
DECLARE @PINChangeFlag char(1)
SET @PINChangeFlag = 'SSnumpinVRU.PINChangeFlag'
UPDATE SSnumpin
SET PIN = @PIN,
FirstTimeFlag = @FirstTimeFlag,
CallCounter = @CallCounter,
Lastname = @LastName,
FirstName_MiddleInitial = @FirstName_MiddleInitial,
EmpNum = @EmpNum,
PINChangeFlag = @PINChangeFlag
This is all going to be a part of the same dts package using execute SQL tasks and data transforms (SQL2K).If there is a better way, or even a cleaner code, it would help alot. Thanks, Pat
Edited by - pop on 06/25/2002 1:31:38 PM
Edited by - pop on 06/25/2002 1:32:24 PM
June 25, 2002 at 2:35 pm
why use local variables? I do the same thing often, but it's something like:
UPDATE SSnumpin
SET PIN = b.PIN,
FirstTimeFlag = b.FirstTimeFlag,
CallCounter = b.CallCounter,
Lastname = b.LastName,
FirstName_MiddleInitial = b.FirstName_MiddleInitial,
EmpNum = b.EmpNum,
PINChangeFlag = b.PINChangeFlag
from temptable b
where b.PK = ssnumpin.PK
Steve Jones
June 25, 2002 at 2:57 pm
Thanks steve. That's just what I was looking for. That works fine. I was trying to make it too complicated. Any opinion on the dts package. I am dropping and re-creating my temp table every 15 min. and then querying the temp table to update the main table as you described in an execute sql task.
P.S. This is a great site. I think I'll be a regular visitor here.
June 25, 2002 at 3:14 pm
You are welcome and looking forward to seeing you back here. Pls tell your friends and coworkers who use SQL Server
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply