How to change identity through script

Hi All,
I have a Column with identity(1,1) .
Using script how to set that to identity(2,2)
Thanks in advance.
SupriyaAssume your original table with identity (1,1) is called test. You can go
through the script below to change the seed and increment.
CREATE TABLE dbo.Tmp_test
(
id int NOT NULL IDENTITY (2, 2),
string varchar(90) NULL
)
SET IDENTITY_INSERT dbo.Tmp_test ON
IF EXISTS(SELECT * FROM dbo.test)
INSERT INTO dbo.Tmp_test (id, string)
SELECT id, string FROM dbo.test WITH (HOLDLOCK TABLOCKX)
SET IDENTITY_INSERT dbo.Tmp_test OFF
DROP TABLE dbo.test
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||Well, I thought I remembered that you could do this, so I tried to whomp a
little something up. Couldn't get it to work. But then I focus on 2000 docs,
while using 2005. So finally, I looked at the 2005 docs: this is an exact
copy/paste from 2005 BOL: ALTER TABLE:
--
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
--
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'.
--
If the supported syntax doesn't match BOL, we got a problem somewhere. So,
if this is true, then you're stuck creating a new table and doing the
insert.
Sorry,
Jay
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||On Thu, 13 Sep 2007 06:48:38 -0700, Jay wrote:
>Well, I thought I remembered that you could do this, so I tried to whomp a
>little something up. Couldn't get it to work. But then I focus on 2000 docs,
>while using 2005. So finally, I looked at the 2005 docs: this is an exact
>copy/paste from 2005 BOL: ALTER TABLE:
>--
>CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
>CompanyName NvarChar (50))
>INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
>ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
Hi Jay,
If you go to that page in Books Online again, you'll see at the top of
the screen that this applies to SQL Server 2005 Compact Edition. This is
a different product, that is for some mysterious reason documented in
the same version of BOL (and I've lost track of the number of times I
myself felll victim to this misunderstanding - especially since the CE
version of BOL tends to come up before the "regular SQL Server" docs).
Changing identity is only supported on CE, and not on "normal" editions
of SQL Server.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
google for DBCC CHECKIDENT RESEED
isnt this the easiest way?|||does work on 2005.
use tempdb
if object_id('t') is not null drop table t
CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
(10))
insert into t(col2) values('one')
DBCC CHECKIDENT (t, RESEED, 200)
insert into t(col2) values('two')
select * from t
"densial" <densial@.gmail.com> wrote in message
news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
> google for DBCC CHECKIDENT RESEED
> isnt this the easiest way?
>|||I get it now.
This isn't resetting the initial value, or the increment, it is setting the
highest value used so the next value comes in above it.
Not what the OP asked for, usefull, but it doesn't change the increment from
1, to 2.
"Jay" <spam@.nospam.org> wrote in message
news:eGmGiZG%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> does work on 2005.
> use tempdb
> if object_id('t') is not null drop table t
> CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
> (10))
> insert into t(col2) values('one')
> DBCC CHECKIDENT (t, RESEED, 200)
> insert into t(col2) values('two')
> select * from t
> "densial" <densial@.gmail.com> wrote in message
> news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
>> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
>> google for DBCC CHECKIDENT RESEED
>> isnt this the easiest way?
>sql