Problems With Identity Specification - Deleting Rows
Apr 6, 2006
First of all, I'm new to the forums, and I'm still getting started with VBEE and SQL.
I'm building a simple Windows Application that has a SQL Database with three tables, related to each other. In all of them I need to set one column as a simple index for the rows (1, 2, 3... etc), to work as a PK and relate to the other tables' FKs.
I've set the property Identity Specification of the column to Yes, and added some rows of data for testing. If you just keep adding rows, the PK columns work fine, but if you start deleting rows, the numbering gets fragmented, and the relations between the tables lose integrity.
I've tried the MSDN Online Help topics, but they just tell you this:
If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If you want to avoid such gaps, do not use the identity property.
So, my questions are: is there another way around this problem? How to number rows automatically and re-number them when a row is deleted, and keep the table relations working? It has anything to do with constraints?
Sorry if my question was already answered, I really couldn't find anything like my problem searching the forums. Thanks in advance.
(and also, sorry for the bad english... )
View 3 Replies
ADVERTISEMENT
Jul 12, 2007
Hi,
How do I determine what the next "id" will be in a collumn where the Identity Specification is turned on with Identity Increment?
I have two tables, the one contains product information and the other is a photo album. When I create a new product, then I automatically create an associated photo album. It all works fine, but I have found some problems after using this setup for a while to manage products.
For example, say I have created 10 products (id's 1 to 10) , and then I decide to remove the last 5 products from the list. The next time I add a new product, it will be product number 6, but the id will be 11 (seeing that the id's will always be unique). How do I determine what the next Identity value will be seeing that the actual last id might not be the last one that was allocated?
RegardsJan
View 2 Replies
View Related
May 29, 2008
I am not very clear about the use of 'identity specification properties'. There does not seem to be a great use of identity increments and seeds, it seems something that saves manual work, but to a very little extent
Anyone willing to shed some light?
View 1 Replies
View Related
Jan 14, 2007
what happens when a column marked as Identity Specification reaches the limit? for example, I have some code tables using tinyints as keys, the actual number of entries will be 20 or so but there is some volatility, so eventually the 255 limit will be reached, what happens then?
the same thing applies to ints or bigints used as keys, eventually the database must run out of numbers
information will be appreciated
David Wilson.
View 2 Replies
View Related
May 5, 2008
I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have a database table as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4]
Using the myGameRecencyAllBalls table ---
I wish to insert a date into a new row but have not been able to determine how to with the identity specification on the GameId column.
Can anyone please assist?
Thank you.
lpbcorp
sqlCmd.CommandText = "DECLARE @date datetime SET @date = '" + Date +
"DECLARE @lastRowGameId int " +
"DECLARE lastrow_gameidcursor CURSOR SCROLL FOR " +
"(SELECT GameId FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_gameidcursor INTO @lastRowGameId " +
"' INSERT INTO " + DBGameName.ToString() + "RecencyAllBalls.Date VALUES (@date) WHERE GameId = @lastrow_gameidcursor + 1";
sqlCmd.ExecuteScalar();
View 6 Replies
View Related
May 27, 2015
Is there anyway I can change Identity specification on column to yes ? and If I do that it supposed to take data as 1,2,3,4 like that?
But mine is not changing it's come up all data as 0. Is there any other way to do?
View 6 Replies
View Related
Mar 1, 2006
subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.
View 7 Replies
View Related
Sep 24, 2007
Hi,
Using Management Studio, when I import data from one identical database to another, any identity specifications get dropped from the destination database.
(From columns which have them on the source database and which had them on the destination database.)
Any way to avoid this?
View 1 Replies
View Related
Jul 18, 2014
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
View 9 Replies
View Related
May 7, 2008
If I right click on a primary key in SQL Server management studion, I can set the identity specification for a primary keyId so that it is indexible. I do this by setting the properties for "Is Identity", "Identity Increment", and "Identity Seed". This is wonderful, but I have been asked to perform this using a script instead of the SQL server GUI/Sql Server management studio. Is this possible? Does anyone know how to do this? If so, can you show me how?
Ralph
View 1 Replies
View Related
Feb 20, 2007
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table
string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//insert an aerobics record into the aerocibs table
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";
pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty
View 8 Replies
View Related
Jun 18, 2007
Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
View 2 Replies
View Related
Nov 26, 2004
I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?
Thanks
View 8 Replies
View Related
Apr 3, 2007
Hi i'm a learner of sql,
i need help in deleting rows from a table that have multiple occurences of a value.
the table has a column named product_id , i need to find out what rows have the product_id value repeated ( i.e not distinct)
how do i do that help please.
View 1 Replies
View Related
Apr 21, 2008
I have an issue that I don't know how to figure out.
I have a table that has a list of files in it. This table also has GUIDs associated with each file.
Some of these files were found in the emporary internet files folder on the systems scanned.
I want to remove all rows where the file path contains emporary internet files
The problem is that there are other tables where the file GUIDs are referrenced.
So when I try this:
DELETE FROM osScanFile
WHERE (FilePath LIKE '% emporary internet files\%')
I get an error saying that "The DELETE statement conflicted with the REFERENCE constraint "blah blah blah". ...
Basically there are rows in other tables that reference the rows I'm trying to delete in this table.
So how do I get it to delete not only the rows with the search criteria in it but also all rows from all other tables where the rows are linked?
Thanks,
Terry
View 3 Replies
View Related
May 6, 2008
I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten.
There is no Primary Key in the csv file that can be used.
I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates.
When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want.
Any thoughts as to why this needs to be run twice? Or is a better approach available?
Here is my code -
SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*)
INTO temppkgactions
FROM pkgactions
GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1
DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 )
FROM dbo.pkgactions
DROP TABLE temppkgactions
Thanks
View 2 Replies
View Related
May 19, 2006
What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>"
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID"
SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)">
<DeleteParameters>
<asp:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID
DELETE FROM [photo] WHERE [UserID] = @UserID;
DELETE FROM [album] WHERE [UserID] = @UserID;
DELETE FROM [comment] WHERE [UserID] = @UserID;
...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!
View 8 Replies
View Related
Feb 11, 1999
Hai
I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error.
The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows
select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99'
If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time.
Thanks
View 6 Replies
View Related
Nov 17, 1998
This is an imaginary problem while discussing ROWID in ORACLE.
Consider a table without primary key, unique key, uniuqe index.
A row has inserted into the table many times.
I want to delete all but one dulicated rows. With any 'where' clause all rows(duplicated)
will be deleted. In ORACLE i can achieve this using ROWID as follows:
Delete from Table_name
where < all column values >
and ROWID <> ( Select max(rowid) from Table_name where < all column values > )
How can this be achieved in MS SQL Server 6.5 ?
According to Dr. Codd's Golden rules for RDBMS one is that
One should be able to reach each data value in the database by using
table name, row idenfication value and column name.
Does MS SQL Server 6.5 satisfy this requirement ?
Also How many of Dr. Codd's 13 Golden Rules for RDBMS does MS SQL Server 6.5
Satisfy? Which doesn't ?
Any discussion about Codd's Rules is welcome.
- Gunvant Patil
gunvantp@yahoo.com
View 1 Replies
View Related
Aug 27, 2001
I have two tables. Table 1 contains a distinct ID(3,4,5). Table 2 contains multiple ID's(3,3,3,3,3,4,4,4,5,5,5). I want to be able to use a join. If an ID is found in table 2, remove all entries of it. Any ideas? Thanks...
delete b.id
from table1 a join table2 b on a.id = b.id
View 1 Replies
View Related
Aug 6, 2004
hello there,
what is the code to Delete all rows in a Table????
thanks in advance
View 4 Replies
View Related
Feb 27, 2014
In my database, I have a table "tbl_c_extract" that consists of 4 columns that look the following. I'm looking at a daily batch of around 4000 records, of which 150 are likely to be duplicates.
Emp_No varchar(255), Proprietary_ID varchar(255), LeaveDateActual datetime
123456, E123456, 2014-09-27 00:00:00.000
213832, E123456, 2099-12-31 00:00:00.000
213836, E123456, 2014-01-31 00:00:00.000
In the example above, I need to remove 2 of the entries, leaving only the one that with the maximum leave date. In this case, those without a leave date have the 2099 entry.
Using CTE works exactly as I want it to, however SQL Server Agent doesn't seem to like the use of CTE..
Code:
WITH CTE (Proprietary_ID, LeaveDate, RN)
AS
(
SELECT Proprietary_ID, LeaveDate,
ROW_NUMBER() OVER(PARTITION BY Proprietary_ID ORDER BY Proprietary_ID, LeaveDate) AS RN
FROM tbl_c_extract
)
DELETE
FROM CTE
WHERE RN > 1
View 2 Replies
View Related
May 23, 2004
Hi,
I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..................
thnx
View 14 Replies
View Related
Feb 1, 2007
I need to delete some specific rows in Table1
int1 must have the value 1
int2 must have the value 1
int3 must have the value 0
and now we get to the difficult part - first character in the field [Cost No] have to be different from the letter 'B'. [Cost No] have the datatype varchar(20)
I expected the code to look something like this:
DELETE Table1
FROM Table 1
WHERE ([Int1] = 1) AND ([Int2] = 1) AND ([Int3] = 0) AND ((LEFT([Cost No]), 1) <> 'B')
But I get this error message:
The left function requires 2 arguments
What am I doing wrong and what should the right code look like?
View 3 Replies
View Related
Apr 5, 2001
Hi folks,
I need to delete the duplicate rows from a table. How to do that in SQL server 7.0 ? If possible write an example, so that it will be much useful for me..
Thanks for ur help..
rgds,
vJ
View 1 Replies
View Related
Dec 10, 2001
i have 6 read only tables that every night all the data gets dumped and a new updated copy of the data is copied over. the tables range from 500,000 rows to almost 4 million. i have indexes set up on the fields i use to query against. my questions are
1.since i dump all the data every night and replace it, do i need to rebuild the indexes every night or is that done after the data is reentered?
2.i want to use a fill factor on the table since it is read only, but will dumping the data every night and reinputting it have adverse affects with a fill factor?
3.should i be shrinking the database or defragging it everynight cause of my data dumps and reloads?
thanks in advance
spiral
View 1 Replies
View Related
Apr 10, 2003
How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())
increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.
Anyone has an idea?
mipo
View 3 Replies
View Related
Nov 16, 2007
I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse
Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).
Any help would be greatly appreciated.
Thanks,
View 3 Replies
View Related
Jul 16, 2013
i am deleating 8 Million rows from my database,I am wondering how to control T-Log,also I heard something about row lock and table lock
View 4 Replies
View Related
Nov 16, 2007
I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse
Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:
I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).
Any help would be greatly appreciated.
Thanks,
View 10 Replies
View Related
Dec 11, 2007
Hi,
I've deleted a bunch of rows from my table.. So far about 500k from a 9 million row table.. This table is hit frequently
I think maybe my indexes are a mess? Its so slow every page is timing out .. im now urgently trying to figure something out to resolve ..
ive done UPDATE STATISTICS on the table .. now its probably best I run some command on the indexes directly?
Any help very much appreciated.. !!!
mike123
View 10 Replies
View Related
Dec 7, 2006
hi there!
im having problems deleting rows in a reference table. is there any tools which tables to delete first before deleting the rows in the table which contains the primary key?
i have a lot of tables let say over 300 so its hard for me to guess which comes first... what should i keep in mind deleting rows with a referential integrity?
thank...
View 7 Replies
View Related
Jul 18, 2007
Hi,
I hope some of you can help me with that
I made an application that insert some data to MS SQL 2005 DB Express Edition. One of columns in my database store text. The content of that field must, beyond the existing text, append the current id to its text string. Practically, it means that if on row nr 15 I store text value "15text", on the next row i will store "16text". I figured out that I can get value of max ID by creating following stored procedure.
USE [tracking_db]
GO
/****** Object: StoredProcedure [dbo].[spMaxId] Script Date: 07/18/2007 09:31:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spMaxId]
@maxId integer output
as
SELECT @maxId= MAX(id) FROM FILES_TABLE
Once i get the maxID value i simply concat max id and string like:
string.Concat(max_id.ToString(), file_name);
where "max_id is integer return value from stored procedure and "file_name" is a string to rename like "max_id+file_name"
Two problems occur!!!
Problem nr 1.
Since I use to insert 10 new rows each time, the values from 0-9 are appended to text like "(0-9)text"
Problem nr 2.
If I delete some rows, ID does not get update. It means that after deleting all rows from table, next inserted item gets last existed ID before delting +1. New inserted item should get value 1 since table is empty after deleting all rows from it!!!
Hope some of you has any idea what to do
Muris
View 6 Replies
View Related
