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>
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 recoverabilityWe 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Otherwise 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I'm not Vladen and not always My humble apologies Gila
If the insert fails, for any reason, then the delete shouldn't run. No argument from me
that is why I mentioned XACT_ABORTI'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]
 
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]
 
REFERENCES [dbo].[ServiceGroups] ([ServiceGroupSys]
 
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