Resorting a report by Column click

have a report that has about 7 columns
I would like to enable the user to be able to resort the report by simply
clicking on a column heading. For example if my Column names are
County Organization Name Program Name
I would the user to be able to click the County Column header and re-sort
the entire report in a descending alpha order and then also click
Organization name column header to re-sort by column name.
Anyone have any ideas of how this might be accomplished?Various approaches for RS 2000 have been discussed on this newsgroup. Search
the newsgroup for "dynamic sort column" or similar keywords.
You may also want to check a sample posted on GotDotNet:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
> have a report that has about 7 columns
> I would like to enable the user to be able to resort the report by simply
> clicking on a column heading. For example if my Column names are
> County Organization Name Program Name
> I would the user to be able to click the County Column header and re-sort
> the entire report in a descending alpha order and then also click
> Organization name column header to re-sort by column name.
> Anyone have any ideas of how this might be accomplished?|||There are also several more examples on www.msbicentral.com
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
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:up5254EHFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Various approaches for RS 2000 have been discussed on this newsgroup.
> Search the newsgroup for "dynamic sort column" or similar keywords.
> You may also want to check a sample posted on GotDotNet:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
> news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
>> have a report that has about 7 columns
>> I would like to enable the user to be able to resort the report by simply
>> clicking on a column heading. For example if my Column names are
>> County Organization Name Program Name
>> I would the user to be able to click the County Column header and re-sort
>> the entire report in a descending alpha order and then also click
>> Organization name column header to re-sort by column name.
>> Anyone have any ideas of how this might be accomplished?
>|||Ok I went to the link that you posted below. I downloaded the sample report
and am trying to figure it out. Where is the code other other event that
does the re-sort?
I copied the same report parameters into the project I'm working on and they
didn't work. Of course, i taylored the fields in the database to field names
I have in my DB, but I can't get the resort to work. There's no readme or
other documentation, so i'm hoping someone can help me out...
"Robert Bruckner [MSFT]" wrote:
> Various approaches for RS 2000 have been discussed on this newsgroup. Search
> the newsgroup for "dynamic sort column" or similar keywords.
> You may also want to check a sample posted on GotDotNet:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Report Guy" <Report Guy@.discussions.microsoft.com> wrote in message
> news:F6EA8E3B-5810-4914-82D7-520C749E4B11@.microsoft.com...
> > have a report that has about 7 columns
> >
> > I would like to enable the user to be able to resort the report by simply
> > clicking on a column heading. For example if my Column names are
> > County Organization Name Program Name
> >
> > I would the user to be able to click the County Column header and re-sort
> > the entire report in a descending alpha order and then also click
> > Organization name column header to re-sort by column name.
> >
> > Anyone have any ideas of how this might be accomplished?
>
>

Resize Report Column Width when Hidden

I have a report that has a certain number of columns that are hidden and when hidden they leave a lot of white space. Is there a way to detect when they are hidden and set the width property to 0 and then reverse the value when they are visible?I used a list box with embedded list boxes to control the column widths automatically. Now the problem is that If I give column headers in the list they do not shrink and I don't want the data repeated above each group either. Any ideas?sql

Resetting Key Column Numeration from SQL Tables

I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?

You have to use the DBCC Checkpoint command

Refer this: http://www.sqlteam.com/item.asp?ItemID=8003

|||Check out BOL for DBCC CHECKIDENT command.|||

Another way to do this is to use Truncate Table Command rather than delete.

This will reset identity.

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

resetting identity columns

Is there a way besides truncate table to reset the identity column of a tabl
e?
Thanks,
JoeLook at DBCC CHECKIDENT in Books Online.
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Jaylou,
Yes. DBCC CHECKIDENT ...RESEED argument.
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Thank you!
I knew there was something but I couldn't find it.|||Thank you!
I knew there was something but I couldn't find it.

Reseting Identity Column using SQL

Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)

Thanks, radco

Take a look at the following pointer:http://msdn2.microsoft.com/en-US/library/ms176057.aspx

Reset the Identity Increment

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.

Ryan KennedyCheck out the DBCC CHECKIDENT command in google^h^h^h^h^h^h Books Online|||Also look at the TRUNCATE statement.

"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:53dPb.1926$kH2.252@.nwrdny01.gnilink.net...
> Reset the Identity Increment

reset table identity in TSQL

I have a table set up with a primary key identity column. Most of the time
this works fine, but there are occasions where I need to update a record and
set the primary key to a number that is not in the identity sequence. For
instance the identity value is 160001 and I need to manually update a
record's primary key to 5000.
So far the only way I know how to do this is to go into Enterprise manager
and select identity to NO on the identity field to save the table, run my
update queries, go back into Enterprise manager and set identity back to YES
and resave. I am sure there must be a way I can put this process into a
script. Is there are way to do this in TSQL without having to use Enterprise
Manager?
Example:
Identity primary key customer_id 160001 needs to be updated to 5000 and
afterwards restore identity back to 160001
thanks,
FrankLookup DBCC CHECKIDENT in SQL Server Books Online.
--
- Anith
( Please reply to newsgroups only )|||look up SET IDENTITY_INSERT in BOL
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||SET IDENTITY_INSERT tableName ON
GO
UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
GO
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
Why? Should you really be using a surrogate if people care what the actual
value is?
In any case, see the following topics in Books Online:
SET IDENTITY_INSERT
DBCC CHECKIDENT
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave.
Please stop using Enterprise Manager to manipulate data. You can do
irrepairable damage, or a whole lot of unnecessary locking, e.g. when the
tool goes behind the scenes and makes a complete copy of a 4 billion row
table without warning you first (many of EM's shortcuts are accomplished
exactly this way).
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
Here's what my approach would be (though I still fail to see the purpose of
assigning some known, meaningful value to a surrogate).
CREATE TABLE blat
(
blatID INT IDENTITY(160000, 1),
cust VARCHAR(32)
)
GO
SET NOCOUNT ON
INSERT blat(cust) VALUES('lobster')
INSERT blat(cust) VALUES('shrimp')
GO
DBCC CHECKIDENT('blat', RESEED, 4999)
GO
INSERT blat SELECT cust FROM blat WHERE blatID = 160001
GO
DELETE blat WHERE blatID = 160001
GO
DBCC CHECKIDENT('blat', 'RESEED', 160000)
GO
INSERT blat(cust) VALUES('squid')
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
Please try this
declare @.i int
declare @.c1 nvarchar(100)
set @.i = 5000
set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
ltrim(rtrim(convert(char,@.i)))+')'
exec sp_executesql @.c1
After updating
declare @.i int
declare @.c1 nvarchar(100)
set @.i = 160001
set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
ltrim(rtrim(convert(char,@.i)))+')'
exec sp_executesql @.c1
Thanks
Hari
MCDBA
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||> SET IDENTITY_INSERT tableName ON
> GO
> UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> GO
This was my initial angle, but regardless of IDENTITY_INSERT setting, I get:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'customer_id'.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Can I put this into a transaction?
begin transaction
set identity_insert tblName ON
UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
commit transaction
Would that work?
thanks,
Frank
"Raymond D'Anjou (raydan)" <raydan@.canatrade.nospamcom> wrote in message
news:ub8z#LavDHA.560@.TK2MSFTNGP11.phx.gbl...
> SET IDENTITY_INSERT tableName ON
> GO
> UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> GO
> "Frank" <frankenberryz@.yahoo.com> wrote in message
> news:eY0Bb.6$1i.21421@.news.uswest.net...
> > I have a table set up with a primary key identity column. Most of the
time
> > this works fine, but there are occasions where I need to update a record
> and
> > set the primary key to a number that is not in the identity sequence.
For
> > instance the identity value is 160001 and I need to manually update a
> > record's primary key to 5000.
> >
> > So far the only way I know how to do this is to go into Enterprise
manager
> > and select identity to NO on the identity field to save the table, run
my
> > update queries, go back into Enterprise manager and set identity back to
> YES
> > and resave. I am sure there must be a way I can put this process into a
> > script. Is there are way to do this in TSQL without having to use
> Enterprise
> > Manager?
> >
> > Example:
> > Identity primary key customer_id 160001 needs to be updated to 5000 and
> > afterwards restore identity back to 160001
> >
> > thanks,
> > Frank
> >
> >
>|||I was thinking that maybe IDENTITY_INSERT doesn't work for updates after I
posted the message.
BOL only mentions that it works for updates.
Sorry, should have put "Untested" before my solution.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eaiPsNavDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > SET IDENTITY_INSERT tableName ON
> > GO
> >
> > UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> > GO
> This was my initial angle, but regardless of IDENTITY_INSERT setting, I
get:
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'customer_id'.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||I get the error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'customer_id'
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#v45XNavDHA.560@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Please try this
> declare @.i int
> declare @.c1 nvarchar(100)
> set @.i = 5000
> set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
> ltrim(rtrim(convert(char,@.i)))+')'
> exec sp_executesql @.c1
> After updating
> declare @.i int
> declare @.c1 nvarchar(100)
> set @.i = 160001
> set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
> ltrim(rtrim(convert(char,@.i)))+')'
> exec sp_executesql @.c1
>
>
> Thanks
> Hari
> MCDBA
>
> "Frank" <frankenberryz@.yahoo.com> wrote in message
> news:eY0Bb.6$1i.21421@.news.uswest.net...
> > I have a table set up with a primary key identity column. Most of the
time
> > this works fine, but there are occasions where I need to update a record
> and
> > set the primary key to a number that is not in the identity sequence.
For
> > instance the identity value is 160001 and I need to manually update a
> > record's primary key to 5000.
> >
> > So far the only way I know how to do this is to go into Enterprise
manager
> > and select identity to NO on the identity field to save the table, run
my
> > update queries, go back into Enterprise manager and set identity back to
> YES
> > and resave. I am sure there must be a way I can put this process into a
> > script. Is there are way to do this in TSQL without having to use
> Enterprise
> > Manager?
> >
> > Example:
> > Identity primary key customer_id 160001 needs to be updated to 5000 and
> > afterwards restore identity back to 160001
> >
> > thanks,
> > Frank
> >
> >
>|||Step 1: Make an INSERT script for the row with PK value 5000.
Step 2: Delete the existing row with PK value 160001
Step 3: Set IDENTITY_INSERT for the table ON
Step 4: Run the INSERT script for PK 5000
Step 5: Set IDENTITY_INSERT for the table OFF
Step 6: Run DBCC CHECKIDENT(<tablename>, RESEED, 16000)
Hope this helps.

reset sql 2000 sequential number

Hello, I have a table where I'm deleting the contents before populating the table with new data. I have an ID column that is autogenerating a sequential number. I would like to reset this number back to 1 when I delete the contents of the table. How can this be accomplished?you could TRUNCATE the table. check out Books On Line for more info.|||Hey ndinakar! Thanks for responding. That's what I came up with too. But, I've heard that truncating a table was bad news; not a best practice. All I know is that it works so I will go with it. Have a good one.|||If TRUNCATE TABLE had not worked in your scenario (and in somescenarios it will not), you can delete all of the records in the tableand then runDBCC CHECKIDENT to reset the identity value.

Reset Primary ID back to 1

Reset the Identity Increment
------------------------

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.The only way to do that is to use truncate table instead of delete.
You need additional rights to be able to execute truncate table statement.

Good Luck.

Irina.|||Yes, of course you can use truncate. It will delete and reseed the identity columns.
It is also more efficient way to save the resources.

But, if you also want to use delete, you can reset the identity column by running the following command:

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Hope to help.

Reset IDENTITY seed

Hello,
Can I reset the IDENTITY seed of a Table column without delete/drop the table?
I want to delete all the table rows, restore de seed, and restore abackup made on a XML (using SET IDENTITY_INSERT Table ON)
I cant drop the table due to acount restricctions.
regards,
Edu
You could try
TRUNCATE TABLE MyTable|||Check outDBCC CHECKIDENT.|||Thanks,
DBCC CHECKIDENT. works fine!
Regards,
Edu

Reset Identity DataType

How can I reset an identity Int column back to start with 1 if I remove all rows from the table?

Thanks,TRUNCATE TABLE tablename

Reset Identity Column Counter.

Is there a way to delete all items from a table that has an identity column and to reset the counter for all new insertions so that they begin at '1' again?

TRUNCATE command resets identity counter.

http://msdn2.microsoft.com/en-us/library/ms177570.aspx

It's also minimally logged and will run faster than the DELETE statement

|||

If TRUNCATE TABLE MyTable does not work for you, let us know.

There are other options if necessary.

|||Although I am able to delete all rows from the table (using 'delete from sometable'), attempting to truncate (using 'truncate table sometable') returns the error: 'Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'sometable' because it is being referenced by a FOREIGN KEY constraint.

The other table that references 'sometable' is also empty and was successfully truncated. It is also the only table that shows up in the dependencies dialog.

|||

A FK needs to be disabled (or removed) before executing the TRUNCATE command (this is because, in part, of the minimal logging that allows TRUNCATE to operate so quickly)|||

See http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1258966&SiteID=17&pageid=0#1258966

Reset Identity Column

Hello,
I have a database table that I will delete all the
contents from regularly. I have an identity column that
I would like to start counting from 1 after deleting all
the records. However it keeps counting from the last
number it used even though all the records are deleted.
Is there anyway to reset the Identity number.
Thanks!
Either truncate the table ( it will automatically be reset)
or
DBCC checkident with a reseed parameter ( this is doc'd in books online..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:2bd401c4287c$8a1ba5e0$a601280a@.phx.gbl...
> Hello,
> I have a database table that I will delete all the
> contents from regularly. I have an identity column that
> I would like to start counting from 1 after deleting all
> the records. However it keeps counting from the last
> number it used even though all the records are deleted.
> Is there anyway to reset the Identity number.
> Thanks!
sql

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then remov
ed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.
Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then rem
oved. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has
an identity seed.
These records had to be deleted, and I would like to begin the numeric seque
nce at a point as if the records had never been added. E.g., before the inco
rrect records were added, the value of this column was 2500. Two hundred rec
ords were added, then remov
ed. I would like the next value to be 2501, however new records are starting
at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the val
ue, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution
...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that h
as an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at
a point as if the records had never been added. E.g., before the incorrect records
were added, the value of this column was 2500. Two hundred records were added, then
rem
oved. I would like the next value to be 2501, however new records are starting at 2701, afte
r the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the v
alue, or is there another way?
> Thanks for any assistance on this.

Reset Identity Column

Hello,
I have a database table that I will delete all the
contents from regularly. I have an identity column that
I would like to start counting from 1 after deleting all
the records. However it keeps counting from the last
number it used even though all the records are deleted.
Is there anyway to reset the Identity number.
Thanks!Either truncate the table ( it will automatically be reset)
or
DBCC checkident with a reseed parameter ( this is doc'd in books online..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Chuck" <anonymous@.discussions.microsoft.com> wrote in message
news:2bd401c4287c$8a1ba5e0$a601280a@.phx.gbl...
> Hello,
> I have a database table that I will delete all the
> contents from regularly. I have an identity column that
> I would like to start counting from 1 after deleting all
> the records. However it keeps counting from the last
> number it used even though all the records are deleted.
> Is there anyway to reset the Identity number.
> Thanks!

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:
> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.