|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
IDENTITY_INSERTI use vb.net and i have a table in sqlserver 2000.
I'd like to update a value of an identity column. How can i update the value ? Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. how can do it ? what is the command ? Thank you Luca Hi
No, you cannot update an identity property. You can delete the row and insert into with the options that you mentioned. <luca.g***@gmail.com> wrote in message Show quote news:1120737755.114189.107850@g47g2000cwa.googlegroups.com... > I use vb.net and i have a table in sqlserver 2000. > I'd like to update a value of an identity column. > How can i update the value ? > Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. > how can do it ? what is the command ? > Thank you > Luca > You use "SET IDENTITY_INSERT... ON" to insert values in an identity
column.You can not update values in this type of column. AMB Show quote "luca.g***@gmail.com" wrote: > I use vb.net and i have a table in sqlserver 2000. > I'd like to update a value of an identity column. > How can i update the value ? > Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. > how can do it ? what is the command ? > Thank you > Luca > > Technically, you cannot update the value of an identoty column. However, if
you must, you may: 1. Using EM (or perhaps ALTER TABLE..) drop the identity property of the column 2. Make your updates to the values 3. Re-add the identity property 4. Using EM or DBCC CHECKIDENT, reset the next seed value. For details on the issues involved, read up on "IDENTITY (Property)" in SQL Server Books Online. Good luck. <luca.g***@gmail.com> wrote in message Show quote news:1120737755.114189.107850@g47g2000cwa.googlegroups.com... > I use vb.net and i have a table in sqlserver 2000. > I'd like to update a value of an identity column. > How can i update the value ? > Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. > how can do it ? what is the command ? > Thank you > Luca > If this identity column is a primary key, then remember that you also need
to update the foreign keys in related tables, otherwise your referential integrity is broken. If there are referential integrity constraints involved, then they will need to be dropped prior and then re-created afterward as well. Show quote "JT" <some***@microsoft.com> wrote in message news:%23eUHCpvgFHA.1412@TK2MSFTNGP09.phx.gbl... > Technically, you cannot update the value of an identoty column. However, if > you must, you may: > > 1. Using EM (or perhaps ALTER TABLE..) drop the identity property of the > column > 2. Make your updates to the values > 3. Re-add the identity property > 4. Using EM or DBCC CHECKIDENT, reset the next seed value. > > For details on the issues involved, read up on "IDENTITY (Property)" in SQL > Server Books Online. Good luck. > > <luca.g***@gmail.com> wrote in message > news:1120737755.114189.107850@g47g2000cwa.googlegroups.com... > > I use vb.net and i have a table in sqlserver 2000. > > I'd like to update a value of an identity column. > > How can i update the value ? > > Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. > > how can do it ? what is the command ? > > Thank you > > Luca > > > > As others mentioned, a column with an identity property cannot be updated.
If you must support updates in that column, you may want to consider maintaining your own custom sequencing mechanism instead, e.g., -- infrastructure create table seq(val int not null); insert into seq values(0); go create proc usp_getseq @val as int output as update seq set @val = val = val + 1; go -- usage begin tran declare @i as int; exec usp_getseq @i output; insert into t1(key, other columns) values(@i, other values); commit tran Show quote news:1120737755.114189.107850@g47g2000cwa.googlegroups.com... >I use vb.net and i have a table in sqlserver 2000. > I'd like to update a value of an identity column. > How can i update the value ? > Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. > how can do it ? what is the command ? > Thank you > Luca > Maybe the question we should be asking the poster is:
Why do you want to update an Identity column? <luca.g***@gmail.com> wrote in message Show quote news:1120737755.114189.107850@g47g2000cwa.googlegroups.com... >I use vb.net and i have a table in sqlserver 2000. > I'd like to update a value of an identity column. > How can i update the value ? > Can i set IDENTITY_INSERT OFF and after set IDENTITY_INSERT ON. > how can do it ? what is the command ? > Thank you > Luca > I have 2 different programs with 2 different databases.
The first database where i have just the new table without data and the second one where i have all. I must take all data from the second database and insert in teh second. The databases have similar table but not egual and i made a program to pass the data. But i have some identity column in the second databases that i must update with the data of the first. For example : in the original database i have TABLE CUSTOMERS - COLUMN ID WITH VALUE 1 2 5 6 the save data i must put in the aother databases. Is it all right ? Excuse my english :-) If you insert into a table, but do not specify a value for the identoty
column, it will be automatically assign one. When selecting from the source table, specify all columns, except for the identity. <luca.g***@gmail.com> wrote in message Show quote news:1120747339.740100.49150@g43g2000cwa.googlegroups.com... > I have 2 different programs with 2 different databases. > The first database where i have just the new table without data and the > second one where i have all. > I must take all data from the second database and insert in teh second. > The databases have similar table but not egual and i made a program to > pass the data. > But i have some identity column in the second databases that i must > update with the data of the first. > For example : > in the original database i have > TABLE CUSTOMERS - COLUMN ID WITH VALUE > 1 > 2 > 5 > 6 > the save data i must put in the aother databases. > > Is it all right ? > Excuse my english :-) > Then you can set "set indentity_insert db1.owner.tbl on" in db1 and insert
from the table in db2. use db1 go set identity_insert db1.dbo.tbl1 on go insert into dbo.tbl1 (c1, c2, ..., cn) select c1, c2, ..., cn from db2.dbo.tbl1 go set identity_insert db1.dbo.tbl1 off go AMB Show quote "luca.g***@gmail.com" wrote: > I have 2 different programs with 2 different databases. > The first database where i have just the new table without data and the > second one where i have all. > I must take all data from the second database and insert in teh second. > The databases have similar table but not egual and i made a program to > pass the data. > But i have some identity column in the second databases that i must > update with the data of the first. > For example : > in the original database i have > TABLE CUSTOMERS - COLUMN ID WITH VALUE > 1 > 2 > 5 > 6 > the save data i must put in the aother databases. > > Is it all right ? > Excuse my english :-) > > can i set it in my program in vb.net ?
qhat is the command ? thank you |
|||||||||||||||||||||||
Other interesting topics