update statement

  • 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.

  • 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

    steve@dkranch.net

  • 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

  • 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

    steve@dkranch.net

  • 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.

  • You are welcome and looking forward to seeing you back here. Pls tell your friends and coworkers who use SQL Server

    Steve Jones

    steve@dkranch.net

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

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