I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.
View 3 RepliesI guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
Thanks.
Hello, Can I reuse the deleted primary key id? I'm using SQLServer2005. cheers,imperialx
View 6 Replies View RelatedHello,
I have an ID column which is an Identity column, and it counts from 1 upwards.
If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.
Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?
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)
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
Hi,
I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!
Thanks in Advance
Hi,
I am currently working on a MS SQL server 2000.
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do this?
Thanks
hi there,
by mistake i deleted tables in the DB, i heard once that there is a way to retireve it after it has deleted, from the temp or something like that
so is there a way?
Thanks
Hello All.
Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.
I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.
Thank you.
Best regards
hi
for after trigger the records stored in followig table
inserted and deleted table.
but i want to know where this tables physically stored ...i mean in which database master or some other database?
and 2nd thing tigger fired for each row or for only insert,delete,update statement?
thanx
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
Hi all
I would like to know if its possible to "Save" records when they get deleted.
For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...
In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.
All this should go to a seperate database or seperate table, it doesnt really matter.
My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
It happened accidently that we deleted records from a table in SQL server 2005 DB. We never took a full backup of the DB till then. Is there any way that we can recover the deleted records. Logs files are still present. ( *mdf and *ldf ).
View 3 Replies View Relatedhello forum
Is there way to undelete deleted records from a table? - For security reasons I need to delete records without removing them out of the database.
In other words: target is to design a procedure to delete records only logical.
I had the idea to use a field, saying record is deleted or not.
This field would control if data is hidden (logical deleted) or not. To undelete a record, only this field would change its state.
But it becomes difficult if you have many FK to logical deleted data.
Is there a better or easier way to do this?
Thanks for you help !!
Hello,
Can any one tell me ..is there any restriction on the total number of records that can be added to a table ( SQL Server 2005) .Because .. i added 100 records to a table through my program..but i was able to c ..only the last 20 records which is being inserted..can any one tellme any way to increase the total number of records..
pls..help me..solution is required immediately.........
Regards,
Sweety
Hi,
i have deleted 5 to 10 records in a table. is there any way to retrieve the deleted records?
please help me in this regards.
Thanks in Advance.
M.ArulMani
I want to delete some records from dbo.ABC table using where clause.
And would like to restore the deleted records back into the same table.
Hello,
I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though.
Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell.
Thanks for any help!
-Tom.
UPDATE H_MyTable
SET HIST_END_DT = @tran_date
FROM H_MyTable his
INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and
(his.PrimaryKey2 = del.PrimaryKey2)
INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and
(his.PrimaryKey2 = ins.PrimaryKey2)
WHERE (his.HIST_END_DT is null)
and ((IsNull(del.PrimaryKey1, -918273645) <>
IsNull(ins.PrimaryKey1, -918273645)) or
(IsNull(del.PrimaryKey2, -918273645) <>
IsNull(ins.PrimaryKey2, -918273645)) or
(IsNull(del.Column3, -918273645) <>
IsNull(ins.Column3, -918273645)))
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end of it a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Mnay thanks for your help!
av
hi all
i deleted 8 fields from a table and i need to retrieve them back, is there a way to do that.
please help it is very urgent
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
Hi there,Could somebody post some simple example how one can refer toa column in the 'deleted' temporary table within a trigger definition ?Should the 'deleted' and 'inserted' temp tables be declared, or they are'implied' ?I keep having this msg when trying to create a trigger"The column prefix 'deleted' does not match with a table name or alias nameused in the query"Thanks for your inputs,Eddy.
View 2 Replies View RelatedFor some reason in Enterprise Manager for SQL Server 2000, I cannotput the following line into a trigger:select * into #deleted from deletedWhen I hit the Apply button I get the following error:Cannot use text, ntext, or image columns in the 'inserted' or'deleted' tablesThis seems like a weird error, since I am not actually doing anythingto the inserted or deleted tables, I am just trying to make a tempcopy.I have another workaround but I am just curious why this happens.Thanks,Rebecca
View 3 Replies View RelatedIs there a configuration or a trick to improve the speed of the access to inserted and deleted tables whithin a trigger? Whenever a trigger is called, the access to inserted or deleted constitute approximatly 95% of the execution time.
Is there a way to have access to inserted and to deleted improved other than copying the data to another table?
I have deleted some data from some table. I dont have any backups and the database in simple recovery Mode. How to recover that data ...
View 15 Replies View RelatedWhat is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?Thanks,Keith
View 1 Replies View RelatedHi,
I need to prevent modification/update to a field.
So I created a trigger. To take the data from DELETED table then replace the field data.
However, I have problem with one field which data type is text.
MS SQL always return me this error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I use the following code to take the data from deleted table
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(NOTES )
FROM DELETED
I also tried simple Select statement
Select notes from deleted
What code should I use to take the deleted data
Please help.
Thanks in advance
Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.
View 2 Replies View Related
Hi
If I delete 5 or more columns from a table I get the error "Index was outside the bounds of the array", OK is the only response. I then have to close the report and then open it again.
Easy fix is don't delete more than 5 columns, but my template is setup with 15 columns as it's far quicker to delete them as it is to add them 1 by 1 each time. I've finally got around to looking for a hotfix or something similar but can find no mention of this exact error relating to table column deleting.
Anyone else experience this or know if there is a hotfix for it?
Thanks
I have two tables like hyd,hyd1 but hyd has data ,now I want to delete all rows from hyd at the same time insert deleted rows into hyd1.
View 7 Replies View Related