|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Issuing multiple calls to SET IDENTITY_INSERT ONMy table's primary key is an identity column.
When I delete a row, I first copy it to another table and afterwards delete it from the original table. When I want to restore the row, I use the SET IDENTITY_INSERT statement, in order to avoid getting a new value for the identity column. The only problem is when there are two clients trying to restore rows at the same time - which causes an error, since the SET IDENTITY_INERT ON statement can only be issued on one table at a time. What can I do to fix this problem? Hi Amir
Use Row level locks before inserting a row into the Table. best regards Chandra Show quote "Amir sh*trit" <AmirShit***@discussions.microsoft.com> wrote in message news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@microsoft.com... > My table's primary key is an identity column. > When I delete a row, I first copy it to another table and afterwards delete it > from the original table. > When I want to restore the row, I use the SET IDENTITY_INSERT statement, in > order to avoid getting a new value for the identity column. > The only problem is when there are two clients trying to restore rows at the > same time - which causes an error, since the SET IDENTITY_INERT ON statement > can only be issued on one table at a time. > What can I do to fix this problem? Amir sh*trit wrote:
> My table's primary key is an identity column. SET IDENTITY_INSERT can be used by multiple sessions, even on the same > When I delete a row, I first copy it to another table and afterwards > delete it from the original table. > When I want to restore the row, I use the SET IDENTITY_INSERT > statement, in order to avoid getting a new value for the identity > column. > The only problem is when there are two clients trying to restore rows > at the same time - which causes an error, since the SET > IDENTITY_INERT ON statement can only be issued on one table at a time. > What can I do to fix this problem? table, without a problem. Are you possibly running a web application that is using pooled connections and both clients are getting the same connection? If you're sure each client is using a different connection, then there should be no problem. If you are using the same connection, you may want to spawn temporary, new connections when a restore operation occurs and close them once the restore is complete. Get rid of the IDENTITY Column and come up with a better way of assigning
keys to your rows. Show quote "Amir sh*trit" <AmirShit***@discussions.microsoft.com> wrote in message news:C400EE3C-E209-45F6-809F-CBB33DBC5FAE@microsoft.com... > My table's primary key is an identity column. > When I delete a row, I first copy it to another table and afterwards > delete it > from the original table. > When I want to restore the row, I use the SET IDENTITY_INSERT statement, > in > order to avoid getting a new value for the identity column. > The only problem is when there are two clients trying to restore rows at > the > same time - which causes an error, since the SET IDENTITY_INERT ON > statement > can only be issued on one table at a time. > What can I do to fix this problem? > When I delete a row, I first copy it to another table and afterwards This makes no sense to me. Why not just assign a new IDENTITY value, since > delete it > from the original table. > When I want to restore the row, I use the SET IDENTITY_INSERT statement, > in > order to avoid getting a new value for the identity column. you apparently don't have dependent rows referencing the data in the original table? IDENTITY should be used only as an artificial key - if you care about what the value is then don't use IDENTITY. -- David Portas SQL Server MVP --
Show quote
"David Portas" wrote: Well, I do have related records, thus I can't insert the row with a new > > When I delete a row, I first copy it to another table and afterwards > > delete it > > from the original table. > > When I want to restore the row, I use the SET IDENTITY_INSERT statement, > > in > > order to avoid getting a new value for the identity column. > > This makes no sense to me. Why not just assign a new IDENTITY value, since > you apparently don't have dependent rows referencing the data in the > original table? > > IDENTITY should be used only as an artificial key - if you care about what > the value is then don't use IDENTITY. > > -- > David Portas > SQL Server MVP > -- IDENTITY if I want to keep the relations. It goes like this: I have a table full of Customers rows and another table with CustomersReports rows (which is a child table of the Customers table). When I delete a customer, I don't really delete it, but rather move it to an archive table along with it's related CustomersReports child rows. In another scenario, I might want to restore the Customer row to it's original table, and restore it's related CustomersReports rows as well. If I will restore the customer by assigning it a new ID, I will be compelled to modify the foreign key in the child table as well. I prefer to avoid it if possible. Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your
queries to include only Archive = 'Y'. The way you're doing it now, you're leaving a lot of orphaned rows in related tables. From what you've explained you don't even have Foreign Key constraints set up on these tables, and won't be able to apply them at any point because of the manner in which you've set this up. Show quote "Amir sh*trit" <AmirShit***@discussions.microsoft.com> wrote in message news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@microsoft.com... > > > "David Portas" wrote: > >> > When I delete a row, I first copy it to another table and afterwards >> > delete it >> > from the original table. >> > When I want to restore the row, I use the SET IDENTITY_INSERT >> > statement, >> > in >> > order to avoid getting a new value for the identity column. >> >> This makes no sense to me. Why not just assign a new IDENTITY value, >> since >> you apparently don't have dependent rows referencing the data in the > > original table? >> >> IDENTITY should be used only as an artificial key - if you care about >> what >> the value is then don't use IDENTITY. >> >> -- >> David Portas >> SQL Server MVP >> -- > > Well, I do have related records, thus I can't insert the row with a new > IDENTITY if I want to keep the relations. > It goes like this: I have a table full of Customers rows and another table > with CustomersReports rows (which is a child table of the Customers > table). > When I delete a customer, I don't really delete it, but rather move it to > an > archive table along with it's related CustomersReports child rows. > In another scenario, I might want to restore the Customer row to it's > original table, and restore it's related CustomersReports rows as well. > If I will restore the customer by assigning it a new ID, I will be > compelled > to modify the foreign key in the child table as well. > I prefer to avoid it if possible.
Show quote
"Michael C#" wrote: I actually do have foreign key constrains, and when I'm moving a row to the > Add a CHAR(1) column called "Archive" and set it to 'Y' or 'N'. Adjust your > queries to include only Archive = 'Y'. The way you're doing it now, you're > leaving a lot of orphaned rows in related tables. From what you've > explained you don't even have Foreign Key constraints set up on these > tables, and won't be able to apply them at any point because of the manner > in which you've set this up. > > "Amir sh*trit" <AmirShit***@discussions.microsoft.com> wrote in message > news:8578B5F3-08BF-4AB1-A950-8F5BE57AD138@microsoft.com... > > > > > > "David Portas" wrote: > > > >> > When I delete a row, I first copy it to another table and afterwards > >> > delete it > >> > from the original table. > >> > When I want to restore the row, I use the SET IDENTITY_INSERT > >> > statement, > >> > in > >> > order to avoid getting a new value for the identity column. > >> > >> This makes no sense to me. Why not just assign a new IDENTITY value, > >> since > >> you apparently don't have dependent rows referencing the data in the > > > original table? > >> > >> IDENTITY should be used only as an artificial key - if you care about > >> what > >> the value is then don't use IDENTITY. > >> > >> -- > >> David Portas > >> SQL Server MVP > >> -- > > > > Well, I do have related records, thus I can't insert the row with a new > > IDENTITY if I want to keep the relations. > > It goes like this: I have a table full of Customers rows and another table > > with CustomersReports rows (which is a child table of the Customers > > table). > > When I delete a customer, I don't really delete it, but rather move it to > > an > > archive table along with it's related CustomersReports child rows. > > In another scenario, I might want to restore the Customer row to it's > > original table, and restore it's related CustomersReports rows as well. > > If I will restore the customer by assigning it a new ID, I will be > > compelled > > to modify the foreign key in the child table as well. > > I prefer to avoid it if possible. > > Hi. archive, I move all of it's related child rows as well (as I explaind before). Besides, Adding a column to the original table costs alot more than moving rows to the archive - both in memory resources and performence. Managing the table this way is also eazyer. Thanks anyway. "Amir sh*trit" <AmirShit***@discussions.microsoft.com> wrote in message I missed your second post that explains how you're also keeping duplicates news:2EC17FAD-43E6-41E8-9F6B-F5CACF4912FC@microsoft.com... > Hi. > I actually do have foreign key constrains, and when I'm moving a row to > the > archive, I move all of it's related child rows as well (as I explaind > before). > Besides, Adding a column to the original table costs alot more than moving > rows to the archive - both in memory resources and performence. > Managing the table this way is also eazyer. > Thanks anyway. of all your other tables as well to hold copies of your records. I'm interested in learning more about how adding a CHAR(1) column to a single table is much less efficient than maintaining and administering a complete duplicate schema and writing additional code to move rows from one schema to the other each time you want to eliminate them from your reports. I'm a little surprised you find it "easier" to implement code that does this: INSERT INTO copy_of_schema_table1 SELECT * FROM real_schema_table1 WHERE MainID = 100 INSERT INTO copy_of_schema_table2 SELECT * FROM real_schema_table2 WHERE MainID = 100 --repeat for each table... DELETE FROM real_schema_table2 WHERE MainID = 100 DELETE FROM real_schema_table1 WHERE MainID = 100 --repeat for each table... All this to archive One set of related rows. Ahhh, probably better wrap all of these INSERTs and DELETEs into a single transaction, so you don't end up with out-of-sync schemas. Oh yeah, can't forget the IDENTITY_INSERT statements. And it's a 'simple' matter of doing the reverse to "un-archive" it. Yet something like this is 'inefficient'? UPDATE schema_table1 SET Archive = 'Y' WHERE MainID = 100 To "archive" a record, and UPDATE schema_table1 SET Archive = 'N' WHERE MainID = 100 To "un-archive" it. Wow. As they say, to each his own, and whatever you find most clever. On Sun, 08 May 2005 11:25:15 -0700, Amir sh*trit wrote:
> Hi. Although I fully agree with Michael and David - this is just a bad idea,> I actually do have foreign key constrains, and when I'm moving a row to the > archive, I move all of it's related child rows as well (as I explaind before). > Besides, Adding a column to the original table costs alot more than moving > rows to the archive - both in memory resources and performence. > Managing the table this way is also eazyer. > Thanks anyway. as far as I can see, you could do it using application locks. Like this: >> CREATE PROCEDURE NeverRunsInParallelAS DECLARE @result int EXEC @result = sp_getapplock @Resource = 'myLock', @LockMode = 'Exclusive' IF @result => 0 BEGIN -- Do your thing, secure in the fact that -- no other connection will do it at the same -- time. END EXEC sp_releaseapplock @Resource = 'myLock1' << HTH, Andrés Taylor > Well, I do have related records, thus I can't insert the row with a new In fact it should be easy to do this. See the example below. However, I > IDENTITY if I want to keep the relations. entirely agree with Michael. It's unnecessary and inefficient to move data around in this way. Copying data from one table to another is a lot more expensive than adding a one-byte column by any measure that I can think of. CREATE TABLE Customers (cust_id INTEGER IDENTITY PRIMARY KEY, cust_name VARCHAR(50) NOT NULL UNIQUE /* Note the alternate key */ ) CREATE TABLE CustomerReports (..., cust_id INTEGER REFERENCES Customrers (cust_id), ...) INSERT INTO Customers (cust_name, ...) SELECT cust_name, FROM CustomersArchive WHERE ... INSERT INTO CustomerReports (cust_id, ... /* other columns */) SELECT C.cust_id, R. ... /* other columns */ FROM CustomerReportsArchive AS R JOIN CustomersArchive AS A ON R.cust_id = A.cust_id JOIN Customers AS C ON A.cust_name = C.cust_name -- David Portas SQL Server MVP --  
Other interesting topics
|
|||||||||||||||||||||||
