Changing column from nvarchar to int

I am upgrading an Access database and need to convert some of the fields
from text to a foreign key from another table. This is no problem and I
already have the query batch to do this. I would like to know if it is
possible to change a column type from nvarchar to int.
Thanks,
Drew LaingAs long as all the NVARCHAR values can be converted to an INT, sure.
ALTER TABLE tablename ALTER COLUMN columnname INT
http://www.aspfaq.com/
(Reverse address to reply.)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>|||Drew,
You can do it if the values are numeric or null. If the value can not be
casted, then you will get an error.
Example:
use northwind
go
create table t (
colA varchar(15)
)
go
insert into t values('1')
insert into t values('2')
insert into t values(null)
go
select * from t
go
alter table t
alter column colA int
go
select * from t
go
alter table t
alter column colA varchar(15)
go
insert into t values ('a')
go
-- will fail
alter table t
alter column colA int
go
select * from t
go
drop table t
go
AMB
"Drew" wrote:

> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
>|||Thanks to both!
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
>I am upgrading an Access database and need to convert some of the fields
>from text to a foreign key from another table. This is no problem and I
>already have the query batch to do this. I would like to know if it is
>possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>

Changing column data type

I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.

ALTER TABLE database_table
ALTER COLUMN my_bit_columnINT;

Thanks.

It should work. The data will change to 0 (if it was False) or 1 (if it was True) after you change your column data type.

Changing between Identity type and Int type

What is the TSQL command to chanage a column of int type to identity type
and vice versa?
Thanks in advance!
KMYou can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.
HTH. Ryan
"krygim" <krygim@.hotmail.com> wrote in message
news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
> What is the TSQL command to chanage a column of int type to identity type
> and vice versa?
> Thanks in advance!
> KM
>|||To add on, if your table is having millions of data do go for Enterprise
Manager, you can mess up your life doing so. It will remain in a hanged
situation. So better do it manual ways like creating a new table and renamin
g
droping the original one.
Thanks,
Sree
"Ryan" wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>
>|||But when I insert the existing data into the new table with the identity
column, how can I make sure that the keys are not changed?
(I am thinking of existing data having integer keys and a lot of unused
numbers due to deletions.)
Ryan wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
>|||> You can't add the IDENTITY property to an existing table -
No ,you CAN
create table #t
(
col1 char(1)
)
insert into #t values ('a')
insert into #t values ('b')
insert into #t values ('c')
alter table #t add col2 int identity(1,1)
select * from #t
Actually you cannot EDIT/ALTER an IDENTITY property
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
> you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||My understanding of the question was to add the identity property to an
existing INT column, admitidly my opening sentance should have read :-
You can't add the IDENTITY property to an existing column
rather than
You can't add the IDENTITY property to an existing table -
Apologies for any mis-understanding. Either way KM now has an answer to his
question.
HTH. Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiljmwAIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> No ,you CAN
> create table #t
> (
> col1 char(1)
> )
> insert into #t values ('a')
> insert into #t values ('b')
> insert into #t values ('c')
> alter table #t add col2 int identity(1,1)
> select * from #t
>
> Actually you cannot EDIT/ALTER an IDENTITY property
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Ryan,
In the Enterprise Manager, I can create an int column with non-contiguous
(and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
records. (Please note that the values are not in ascending order and two of
them are duplicated.) Then later, I can change the column to an identity
column without any value change. Thus I am sure that rather than dropping
and recreating the column, the Enterprise Manager is doing it in another
way. Please correct me know if I am wrong.
KM
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
type
>|||Hi Ferdinand,
To your question:
You should Set identity_insert on on the new table with the identity
before you insert the data.
The Set will last for the session or until you set identity_insert off.
Example:
Create table #source (cola int, colb int)
insert into #source values (1,2)
insert into #source values (3,4)
Create table #dest (cola int identity, colb int)
set identity_insert #dest on
Insert into #dest (cola,colb)
Select cola, colb
From #source
Select *
from #dest
-- drop table #dest
-- drop table #source
"Ferdinand Zaubzer" wrote:

> But when I insert the existing data into the new table with the identity
> column, how can I make sure that the keys are not changed?
> (I am thinking of existing data having integer keys and a lot of unused
> numbers due to deletions.)
>
> Ryan wrote:
>
>|||Run the SQL Profiler as the EM makes the change. You'll be amazed.
ML
http://milambda.blogspot.com/|||> Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
>
Enterprise Manager create a new table, loads data from the old table and
them drops the old table to implement this change. You can verify this with
a Profiler trace or by clicking the 'save change script' button. The end
result is as if the identity property was simply added to the column but the
actual technique used is more involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Krygim" <krygim@.hotmail.com> wrote in message
news:OrPh5wBIGHA.2912@.tk2msftngp13.phx.gbl...
> Ryan,
> In the Enterprise Manager, I can create an int column with non-contiguous
> (and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
> records. (Please note that the values are not in ascending order and two
> of
> them are duplicated.) Then later, I can change the column to an identity
> column without any value change. Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
> KM
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> you
> type
>

Changing all Int to Bigint

Hi,
We need to change all Int data types to Bigint. Considering the number of
tables and View tables, changing manually one by one is not a option. Is
there a nicer way of changing all (hundreds of them) in more automatic way?
YCYou'll have to script this kind of thing. You can start with
INFORMATION_SCHEMA.COLUMNS and filter on DATA_TYPE. You can generate a
bunch of ALTER TABLE statements from that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"YC" <asppsa@.hotmail.com> wrote in message
news:%23NDqO2DhGHA.4452@.TK2MSFTNGP05.phx.gbl...
Hi,
We need to change all Int data types to Bigint. Considering the number of
tables and View tables, changing manually one by one is not a option. Is
there a nicer way of changing all (hundreds of them) in more automatic way?
YC|||On Tue, 30 May 2006 16:05:30 -0700, YC wrote:

>Hi,
>We need to change all Int data types to Bigint. Considering the number of
>tables and View tables, changing manually one by one is not a option. Is
>there a nicer way of changing all (hundreds of them) in more automatic way?
Hi YC,
Tom told you how to do this. But I just have to ask why you want to do
this. I can imagine that the -2,147,483,648 to 2,147,483,647 range is
insufficient for SOME columns - but all'?
Have you considered the impact that this change will have on your DB's
storage requirements? All int columns will double in size - this will
impact storage requirement for tables with integer columns, but for
indexes on integer columns as well (and any nonclustered indexes if the
clustered index of the same table is on an integer column).
How about performance - more bytes per data row (and per index row)
means less rows per page. Means more logical page reads. But also less
cache hits, and hence more physical page reads.
I recommend you to change just the columns that need the ennhanced range
of bigint. Keep other columns as int.
Hugo Kornelis, SQL Server MVP|||I have to agree. One more thing - you'll have to drop any constraints or
indexes that use the columns before you can alter the columns. You'll then
have to replace them when you're done.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p36s72ht36u3slffgfilhi2ic2lhp689tn@.
4ax.com...
On Tue, 30 May 2006 16:05:30 -0700, YC wrote:

>Hi,
>We need to change all Int data types to Bigint. Considering the number of
>tables and View tables, changing manually one by one is not a option. Is
>there a nicer way of changing all (hundreds of them) in more automatic way?
Hi YC,
Tom told you how to do this. But I just have to ask why you want to do
this. I can imagine that the -2,147,483,648 to 2,147,483,647 range is
insufficient for SOME columns - but all'?
Have you considered the impact that this change will have on your DB's
storage requirements? All int columns will double in size - this will
impact storage requirement for tables with integer columns, but for
indexes on integer columns as well (and any nonclustered indexes if the
clustered index of the same table is on an integer column).
How about performance - more bytes per data row (and per index row)
means less rows per page. Means more logical page reads. But also less
cache hits, and hence more physical page reads.
I recommend you to change just the columns that need the ennhanced range
of bigint. Keep other columns as int.
Hugo Kornelis, SQL Server MVPsql

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.
You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'

DROP DEFAULT DF_tblIndividual_fldRenewalStatus

ALTER TABLE tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default

ALTER TABLE tblIndividual
DROP COLUMN fldRenewalStatus
GO

ALTER TABLE tblIndividual
ADD fldRenewalStatus int NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Thoughts?

Thanks

EdwardALTER the table to add a new INT column. Set the value of that column
based on the data in the BIT column. Alter the table to drop the BIT
column. Rename the INT column.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:
>
>EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'
>
>DROP DEFAULT DF_tblIndividual_fldRenewalStatus
>
>ALTER TABLE tblIndividual
>DROP CONSTRAINT fldRenewalStatus_Default
>
>ALTER TABLE tblIndividual
>DROP COLUMN fldRenewalStatus
>GO
>
>ALTER TABLE tblIndividual
>ADD fldRenewalStatus int NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>
>Thoughts?
>
>Thanks
>
>Edward

|||On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>A few weeks ago a client asked me to add a column to a table so I
>created this script:
>
>ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
>CONSTRAINT fldRenewalStatus_Default DEFAULT 0
>
>Now they want to change it from a BIT to an INT, to store an enum.
>Fair enough. However, no matter how much I wrangle with a script, I
>can't find a reliable way to alter the column. I've mixed and matched
>the following and nothing seems to work:

Hi Edward,

You can do as Roy suggests, or you can run the following script:

ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;

ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;

ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;

Running the ALTER COLUMN might take long if you have lots of data!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:06:18 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>You can do as Roy suggests, or you can run the following script:

Much simpler, of course. Somehow I had the idea that bit would not
convert to int. Thanks for the diplomatic correction. 8-)

Roy|||Hugo Kornelis wrote:

Quote:

Originally Posted by

On 2 Jan 2007 09:19:00 -0800, teddysnips@.hotmail.com wrote:
>

Quote:

Originally Posted by

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

>
Hi Edward,
>
You can do as Roy suggests, or you can run the following script:
>
ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;
>
ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;
>
ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;
>
Running the ALTER COLUMN might take long if you have lots of data!

Thanks Hugo - that worked a treat!

Edward

Changind a Primary Key value

Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,
Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:

>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>
sql

Changind a Primary Key value

Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>

Changind a Primary Key value

Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:

>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 10
0
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>