Resetting Identity Values Of PKs
Sep 21, 2000
What is the easiest way in T-SQL to reset the identity values of
the PK columns in a DB. Here's the scenario.
I'm going to be backing up a HUGE DB and then restoring
it to a new server. Once there I will run an SP to remove all the existing
data. The tables are now row-free. However my customer doesn't like that
the first record they add gets PK value of 5,321, or whatever.
After I remove all the data how can I reset the ids?
It would be nice to add some code to my sp_cleardb
that would go back and reset the identity columns
FYI
All of my tables use auto-incrementing identity fields with a seed of 1
1) I know how to do it in enterprise manager, but I need it in scripting
since I'll have to do it on hundreds of tables
2) I tried using alter table to drop the PK constraint, but that requires
knowing the pk constraint name, which is SQL generated and I won't know.
I think I got very close, but the code was just getting too big. I thought,
there must be an easier function to use, or a built in SP, or something?
Anyone know how to script this?
THanks in advance.
Josh
View 2 Replies
ADVERTISEMENT
May 2, 2008
I am trying to reset an IDENTITY RowNumber
back to 1 in a query when the Person's Name is the same. The following query
can be used agains the pubs database in SQL Server.
I am trying to figure out where to place the section for the DBCC CHECKIDENT item in order to reset the RowNumber back to 1 when ever the lname column is the same. For example if the pub database had duplicate records for a given employee the RowNumber would reset back to 1.
CREATE TABLE #RowNumber (
RowNumber int IDENTITY (1, 1),
emp_id char(9) )
--DECLARE @seed int
--SET @seed = 1
--DBCC CHECKIDENT ('#RowNumber', RESEED , @seed) WITH NO_INFOMSGS
INSERT #RowNumber (emp_id)
SELECT emp_id
FROM employee
ORDER BY lname
SELECT RowNumber, e.emp_id, lname, fname, job_id
FROM #RowNumber r JOIN employee e
ON r.emp_id = e.emp_id
ORDER BY RowNumber
DROP TABLE #RowNumber
Ganesh
View 3 Replies
View Related
Apr 18, 2008
I've seen a lot of solutions to reset the an Identity Field, but the one I found in the website I mentioned worked fine in SQL 2005 Express Edition.
http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/
The other solutions in SQL 2005 that use ALTER TABLE and ALTER COLUMN just don't work, I don't no Why.
Paulo Alexandre
View 1 Replies
View Related
May 2, 2001
If there are gaps ( due to deletions ) in indentity column, how do I
resequence the values ? As well as how to reset the last used number ?
Info. is greatly appreciated.
Ivan
View 1 Replies
View Related
Mar 29, 2000
Can anyone tell me of a way to reset the value of an Identity field back to its seed value after all the rows in a given table have been deleted and I try to re-populate it? There are foreign key relationships so I cannot just drop and recreate the table, and I really need to address this issue from within sql, rather than from the application.
Thanks, any and all guidance would be great :)
View 4 Replies
View Related
Oct 16, 2006
hi guys i was wondering if anyone could help me, i have a table with a field called id that did have numbers 1,2,3,4,5,6,7,8 and so on! but after some tinkering i have removed a few value and added more so i now have 1, 4,8,19,20 and so on!i was wondering if i can run a query to update those value and return them to 1,2,3,4,5,6,7,8 and so on?CheersTupps
View 2 Replies
View Related
Nov 15, 1999
I have a database that has an Identity field in one table which has duplicate values. how can this be? and is there any way that this can be adjusted or corrected. (this field is used as the primary key).....UG!
View 1 Replies
View Related
Jan 2, 2008
i have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...
View 5 Replies
View Related
Aug 26, 1998
We are experiencing a problem at more than one site - it has only
just started happening.
We are actually getting duplicate Identity column values in various
tables and the values seem random - its not like the counter just gets
wound back.
We have used dbcc checkident and also bcp out and in the data, which
of course corrected the tables but only temporarily. Our application
is not doing any select into`s - just plain old inserts which for some
reason are allowing dups to be inserted into the tables.
problem sites are either sp3 or sp4.
There are thousands of sites (including most of ours) where there are
no problems like this with this particular app.
Any help from anyone would be most appreciated.
View 1 Replies
View Related
Oct 3, 2005
hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
View 3 Replies
View Related
Dec 28, 2005
If I have an Identity-declared column, the table has lots of rows and I start deleteting records - will any old, deleted identities be re-used (without an explicit re-seed) ?
(I'm talking about numerical Identities, not these GUID things)
View 2 Replies
View Related
Oct 3, 2005
hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
bye
View 1 Replies
View Related
Mar 28, 2008
Going from Oracle to SQL 2000 and want to repeat Oracle process whereby the job_id field is generated using a trigger and a sequence one-up generator. These two values are then combined to create the job_id number. The sequence is simple: a minvalue, maxvalue and increment 1. The trigger is generating the first five characters from yyddd and appending the next sequence value to it.
Example:
JOB_ID: 08088155
YY: (Year = 08)
DDD: (88th day of year)
Next sequence number: (155)
When I create the table in SQL 2000 I will identify the JOB_ID column as an IDENTITY type data field, but is there any way to duplicate this Oracle sequence in SQL?
Any help will be greatly appreciated.
Bill
View 1 Replies
View Related
May 20, 2000
To All,
I have a table [multiple entries over the web] where the identity field values do not appear in order using a simple SELECT [no ORDER BY]. Why?
View 2 Replies
View Related
May 17, 2004
I created table
create table t1
(
oid int identity(1, 1),
f tinyint
)
and need way to fill it as fast as possible with say 100000 sequential values. Field f may stay = 0 but oid should be raising from row to row. How can I do it in MSSQL 2000?
View 3 Replies
View Related
Oct 18, 2005
Hi All,
This is my stored procedure
CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA
END
This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5.....
or is there any chance of ID column having values like 1,2, 4, 6,7,8....
Please reply...
qa
View 2 Replies
View Related
Sep 28, 2006
This is a very basic question on replication.
I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.
Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.
Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-
1. I don't want to use GUID.
2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.
i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.
any help will be highly appreciable.
View 3 Replies
View Related
Apr 29, 2008
Is there any way to get the new identity values created after an OLE DB Destination has finished its inserts?
Basically my problem is this...
My source database has its primary and foreign key columns as a GUID datatype. In my conversion, my destination database will be using INT (identity) columns for primary/foreign keys. How do I propagate the foreign key relationships to the destination database in an SSIS package? So obviously I can use an OLE DB Destination to generate the data with identities for the parent table, but after that I am stuck in how to create the relationships for the child tables.
I know how to do this in TSQL fairly easily (cursor or temp tables), but it doesn't seem so simple in SSIS.
thanks in advance.
View 9 Replies
View Related
May 29, 2012
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
View 4 Replies
View Related
May 27, 2008
Howdy all,
I've run into an interesting scenario that I can't seem to resolve.
We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.
create table ident (seq_id int identity(1,1) primary key NOT NULL)
If I want to grab a new sequence id, I can execute
INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id
All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values.
If the table had one other field in it, I could do the following
create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS
Can this be done if there is only the identity column and if so, someone care to educate me?
View 10 Replies
View Related
Jul 11, 2013
how to reseed for selected basing on their last count.I have written a query to to reseed basing on last count.But for how to do 10 tables at a time .
declare @last int
select @last=max(empid) from Table_1
DBCC CHECKIDENT (Table_1, RESEED, @last)
but how to do for more than 10 tables or more tables...reseeding at one go basing on last count.
View 2 Replies
View Related
Feb 8, 2007
Arif writes "I want to insert values in to a table which is having identity column, how? can you help me out from this problem.
Thanks in advance
Jamal"
View 6 Replies
View Related
Aug 5, 2006
Hi Guys
Is there an easy way to pad identity column values with zeros to the left (0001, 0010,0100, etc) or I will just have to format them before displaying
George
View 3 Replies
View Related
May 27, 2008
Howdy all,
I've run into an interesting scenario that I can't seem to resolve.
We have a table that we are using to create sequence ids. It's literally a table with a single field with the identity value turned on.
Code Snippet
create table ident (seq_id int identity(1,1) primary key NOT NULL)
If I want to grab a new sequence id, I can execute
Code Snippet
INSERT INTO
ident
DEFAULT VALUES
SELECT scope_identity() AS seq_id
All is well and good. However, now let's suppose that instead of just one, I'd like to get a range of values. If the table had one other field in it, I could do the following
Code Snippet
create table ident2 (seq_id int identity(1,1) primary key NOT NULL, placeholder char(1))
GO
;
WITH RECORDS AS
(
SELECT
CAST('A' as char(1)) AS col1
UNION ALL
SELECT
char(ascii(col1) + 1)
FROM
RECORDS
WHERE
col1 < 'C'
)
INSERT INTO
ident2
OUTPUT
INSERTED.seq_id
SELECT
NULL
FROM
RECORDS
Is there a way to do this if there is only the identity column and if so, someone care to educate me?
From a design perspective, all I can say is "it's not mine" but I do have to live with it, or at least my coworker has to live with it. I'm merely trying to prevent them from opening a cursor and calling the default values version 45M times and no, that's not an exageration.
View 2 Replies
View Related
May 8, 2006
Hello,
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
View 1 Replies
View Related
Jul 25, 2015
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
View 2 Replies
View Related
May 8, 2008
Hi,I have a table OutMailDetails with the following fields : OutMailID --- Foriegn Key Name Description
My problem is that i have datas already in this table and the table is related to another table called OutMail through OutMailID field. Please how can i make it (OutMailID field) a primary key now.
View 2 Replies
View Related
Dec 1, 2004
i am using autonumber as a primary key in one table. in my app., row from that table will be moved to another table after some time but that autonumber is important. when there are no any data in first table, and new row is inserted, autonumber starts from 1 which i don't want as there will be data redundancy when it will be moved to another table. so is there any way i can force the autonumber to start from previous value rathe than 1?
View 2 Replies
View Related
Aug 10, 2005
hi!i'm new to sql server and enterprise manager and i accidentally deleted all data in a table. how do i recover them? the table (CaptureManager) is still there but empty. i have the following information:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaptureManager]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[CaptureManager]GO
CREATE TABLE [dbo].[CaptureManager] ( [ID] [tinyint] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Ext] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO
View 4 Replies
View Related
Aug 4, 2004
Hey! I downloaded the sql engine and made a bunch of tables in visual basic. They were called new1, new2, new3, and new4. So I deleted new1.mdf, new1.ldf, new2.mdf, etc... but I notice I still can't recreate them. So I'm thinking there's remnants in master.mdf or something?? How would I go about getting rid of all the old tables, even ones whose names I've forgotten, so I can have a clean slate again? (btw I don't have sql server, just the desktop engine)
View 4 Replies
View Related
Feb 1, 2008
Hello all-
Is it possible to reset the values of DMV stats/counters without restarting the SQL service? I'm looking for something more than dbcc freeproccache...more along the lines of index_usage and some of the OS DMVs.
Cheers,
-Brandon Tucker
View 3 Replies
View Related
Aug 23, 2007
Hello all.
This is the best suited forum I found for my problem, if there's a better suited one please direct me.
I am running SQL Server 7.0 on Windows 2000 Server, and the SA's password has been forgotten and no other employee knows it.
I searched the net for a solution, and I found various pages describing how to reset the SA's password by logging in an administrator, connecting to the server and using a sp_password command.
For some reason, I cannot connect to the server even when I'm logged on as administrator.
Here are the steps I took:
1) I logged in as DOMAINAdministrator at my domain controller.
2) I opened Enterprise Manager and clicked on the server labeled "(LOCAL)". The server is registered to use "Windows NT authentication".
3) Received the following error:"A connection could not be established to (LOCAL) - Login failed for user 'DOMAINAdministrator'.. Please verify that SQL Server is running and check your SQL Server registration properties ... and try again". If there was an option to attach a screenshot, I would, but...
4) I have verified that SQL Server is running.
As I am new to SQL Server, I'm not sure about this, but the SQL Server is called SERVERNET, and the domain I'm logged into is called DOMAIN.
Should I login as an administrator to SERVERNET and not to DOMAIN? Is that possible? "SERVERNET" does not show on the login screen, only "DOMAIN" is listed there.
Any help will be appreciated.
Thanks
View 5 Replies
View Related
Nov 15, 1999
I would like to reset the Identity column in a table so it starts at the begining. I am planning to clean out this table.
Thanks
Gary
View 1 Replies
View Related
