insert into identity column

  • I am trying to do a simple insert into a table with an identity field.  This is the query I'm using

    SET IDENTITY_INSERT Services ON

    GO 

    Begin Tran

    Update services

    set servicesys = 886

    where servicesys = 908

    ---commit

    rollback

    SET IDENTITY_INSERT Services  OFF 

    Everytime I receive the following error:  Server: Msg 8102, Level 16, State 1, Line 1

    Cannot update identity column 'servicesys'.

    I need to create a simple idiot proof query that I can hand to someone without any SQL experience and have them run to change this value.  Any ideas would be greatly appreciated.  thanks

     

  • Does the identity 886 exist elsewhere in the Services table ?


    Mathew J Kulangara
    sqladventures.blogspot.com

  • No, 886 does not currently exist in the ServiceSys column.

  • Far as I know (& I'm sure someone'll happen by & tell me if I'm wrong - ) - you can only use this to insert an explicit value into an identity column - (eg: when filling gaps, reseeding etc..) - don't believe updates on an id column are allowed at all...







    **ASCII stupid question, get a stupid ANSI !!!**

  • I can suggest a (theoritical) solution to the problem without applying any code.... i thing it is not very dificult to implement.

    Write a procedure to:

    BEGIN

    Step 1: Lock the table you want to update for exclusive use.

    Step 2: Change the column attribute Identity to 'No' -- I suppose by acessing some sysX tables.

    Step 3: make the update.

    Step 4: Change the column attribute Identity to 'Yes' -- I suppose by acessing the same sysX tables.

    Step 5: UnLock the table.

    END

    I tried this mannualy (by changing the attribute in Design Mode) and it works.

    I hope... this will help.... the only problem is to... implement it

    ------------
    When you 've got a hammer, everything starts to look like a nail...

  • A few comments on that.

    While you can alter table alter column to add an identity, you can't use alter table alter column to remove the identity propery. What Enterprise manager does is recreate the entire table with the new definition and move the data over.

    Is a very, very bad idea to hack (directly update) the system tables. first, may cause odd side effects or errors in the future, second, won't work with SQL 2005.

    I'd suggest something like this.

    SET IDENTITY_INSERT Services ON

    GO 

    DECLARE @TempHoldingTable (... table definition goes here...)

    INSERT INTO @TempHoldingTable (column list)

    SELECT <columns> from services

    where servicesys = 908

    UPDATE @TempHoldingTable SET servicesys = 886

    DELETE FROM Services WHERE servicesys = 886

    INSERT INTO Services (<ColumnList&gt SELECT <columns> FROM @TempHoldingTable 

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • "I need to create a simple idiot proof query that I can hand to someone without any SQL experience and have them run to change this value.  Any ideas would be greatly appreciated."

    IMHO it is not a good idea at all to allow update of identity column to someone who knows nothing about databases and SQL. What is the reason for such update, if I may ask? BTW, is the identity column also a primary key?

  • I'm with Vladan on this one

    But if you really must do this

    put query in a stored procedure and get whoever to call the procedure

    BEGIN TRANSACTION

    SET IDENTITY_INSERT Services ON

    INSERT INTO services (servicesys, col1, col2 ...)

    SELECT 886, col1, col2 ...

    FROM services

    WHERE servicesys = 908

    SET IDENTITY_INSERT Services  OFF

    DELETE FROM services WHERE servicesys = 908

    COMMIT TRANSACTION

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Yeah, that's better than mine. Why didn't I think of that...

    However

    BEGIN TRANSACTION

    SET IDENTITY_INSERT Services ON

    INSERT INTO services (servicesys, col1, col2 ...)

    SELECT 886, col1, col2 ...

    FROM services

    WHERE servicesys = 908

    IF @@Error !=0

      GOTO Err

    SET IDENTITY_INSERT Services  OFF

    DELETE FROM services WHERE servicesys = 908

    IF @@Error !=0

      GOTO Err

    COMMIT TRANSACTION

    RETURN

    Err:

     RAISERROR('Oops, something went wrong!!!',16,1)

     SET IDENTITY_INSERT Services  OFF

     ROLLBACK

     RETURN

    Otherwise the insert could fail, the delete succeed and the transaction be committed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • quoteOtherwise the insert could fail, the delete succeed and the transaction be committed.

    True Gila, however if the insert fails with a fatal error then the transaction will be aborted by SQL Server and the @@ERROR test will not get executed.

    This is different for non fatal errors but using

    SET XACT_ABORT ON

    will abort the batch either way.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Sorry - this has nothing to do with anything except that I haven't ribbed David in a long time and what with it being a Friday and all....

    David - you really should pay attention - that was GilaMonster who responded to your post - not Vladan...







    **ASCII stupid question, get a stupid ANSI !!!**

  • OK did not engage brain properly

    it is Friday afternoon after all

    Thanks for the reality check Fred.... I mean sushila

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Not always. See below

    CREATE

    TABLE #Temp(

    ID int NOT NULL,

    KeyField int NULL

    )

    GO

    ALTER TABLE #Temp ADD CONSTRAINT

    TempCheck CHECK (KeyField<10)

    GO

    ALTER

    TABLE #Temp ADD CONSTRAINT

    PK_Temp PRIMARY KEY CLUSTERED (ID)

    GO

    BEGIN TRANSACTION

    INSERT INTO #Temp VALUES (1,1) -- valid

    INSERT INTO #Temp VALUES (2,2) -- valid

    INSERT INTO #Temp VALUES (1,3) -- not valid. Primary key violation

    INSERT INTO #Temp VALUES (4,15) -- not valid. Constraint violation

    INSERT INTO #Temp VALUES (5,5) -- valid

    COMMIT TRANSACTION

    SELECT

    * FROM #Temp -- returns 3 rows.

    GO

    The two errors raised are severity 14 (pk violation) and severity 16 (check constraint violation). The statements get terminated, but the batch does not.

    If the insert fails, for any reason, then the delete shouldn't run.

    I'm not a fan of xact_abort, I prefer to manage my errors myself than relying on default behaviour

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • quoteI'm not Vladen and not always

    My humble apologies Gila

    quoteIf the insert fails, for any reason, then the delete shouldn't run.

    No argument from me that is why I mentioned XACT_ABORT

    quoteI'm not a fax (sic) of xact_abort, I prefer to manage my errors myself.

    If there was fatal error, e.g.

    INSERT INTO #Temp VALUES (4,'A')

    then you cannot trap or manage the error in TSQL only on the client end

    I do as you stated and check @@ERROR in multi query procedures myself but most of the time I just ROLLBACK and exit.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • I greatly appreciate all the input.  The table I'm working with is not a system table and here is the script to create it.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ServiceServiceLines_Services]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

    ALTER TABLE [dbo].[ServiceServiceLines] DROP CONSTRAINT FK_ServiceServiceLines_Services

    GO

    /****** Object:  Table [dbo].[Services]    Script Date: 3/31/2006 8:40:04 AM ******/

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Services]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Services]

    GO

    /****** Object:  Table [dbo].[Services]    Script Date: 3/31/2006 8:40:06 AM ******/

    CREATE TABLE [dbo].[Services] (

     [ServiceSys] [int] IDENTITY (100000, 1) NOT NULL ,

     [Service] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [ServiceGroupSys] [int] NOT NULL ,

     [Sequence] [int] NULL ,

     [RequiresPatientSelection] [bit] NOT NULL ,

     [HREF] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [MenuID] [int] NULL ,

     [HelpText] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [ServiceLongName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Services] WITH NOCHECK ADD

     CONSTRAINT [PK_Services] PRIMARY KEY  CLUSTERED

     (

      [ServiceSys]

    &nbsp  ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Services] ADD

     CONSTRAINT [DF__Services__Requir__6521F869] DEFAULT (0) FOR [RequiresPatientSelection]

    GO

     CREATE  UNIQUE  INDEX [IX_ServiceGroups] ON [dbo].[Services]([ServiceGroupSys], [Sequence]) ON [PRIMARY]

    GO

     CREATE  INDEX [IX_MenuID] ON [dbo].[Services]([MenuID]) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Services] ADD

     CONSTRAINT [FK_Services_ServiceGroups] FOREIGN KEY

     (

      [ServiceGroupSys]

    &nbsp REFERENCES [dbo].[ServiceGroups] (

      [ServiceGroupSys]

    &nbsp

    GO

     

    I have made this modification to another database with the same schema.  The change was not that hard but I had to copy the data to a temp table, drop constraints, make changes and rename tables.  I may have done more work than I need to but it was successful in changing the value.  The problem is that I need to be able to hand a script or at least be on the phone walking someone through the process that is not very knowledgeable about SQL.  Not the way I would like to do it but I don't have a choice.  Any suggestions would be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 15 total)

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