473,327 Members | 1,892 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Finding identity column value from a table

Hi,

I have a backup and restore module in my project. The backup uses a typed
dataset object (XSD) to get the data from database and creates a xml file as
the backup file (using the WriteXml method of dataset). When doing the
restore i have to overwrite the data from xml back to database.

these are the steps that i follow.

1. get the data from database.
2. delete the data of step 1.
3. load the xml data.
4. transfer the xml data back on database.

The problem is the master tables have primary key as identity. How do i get
the next identity for a table?
database has over 100 tables with the relation ship created.
all the above four action will happen in transaction.
XSD is created from server explorer in Visual studio.NET.
Database used is SQL 7.

Regards.
Devesh Aggarwal / Hafeez
Nov 20 '05 #1
2 1353
SELECT @@IDENTITY Can retrieve the Identity of the last insertion.

....Ashok

"Devesh Aggarwal" <so*******@unieme.com> wrote in message
news:e7**************@TK2MSFTNGP09.phx.gbl...
Hi,

I have a backup and restore module in my project. The backup uses a typed
dataset object (XSD) to get the data from database and creates a xml file as the backup file (using the WriteXml method of dataset). When doing the
restore i have to overwrite the data from xml back to database.

these are the steps that i follow.

1. get the data from database.
2. delete the data of step 1.
3. load the xml data.
4. transfer the xml data back on database.

The problem is the master tables have primary key as identity. How do i get the next identity for a table?
database has over 100 tables with the relation ship created.
all the above four action will happen in transaction.
XSD is created from server explorer in Visual studio.NET.
Database used is SQL 7.

Regards.
Devesh Aggarwal / Hafeez

Nov 20 '05 #2
Hi,

Thanks for the reply, but that doesn't solve the issue.

The @@identity returns the last identity value for the last insert in the
databse irrespective of any particular table.

But in our case we want the identity value before we insert the record in
the table. We want the last identity value only for a particular table. We
can do that in SQL 2000 using iden_current (tablename). Is there a way to do
the same in SQL 7.

Right now i am using a workaround which i fell is not right as there are
more than 100 tables & the time taken is too high. I insert a dummy record
get the identity using @@identity, delete the dummy record & then insert the
new record from the XML file for that table. I repeat the same for all 108
odd tables.

Any suggestions would be highly appreciated as the performance of the
application has gone down dratically because of this.

Regards,
Devesh Aggarwal

"G.Ashok" <gw******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
SELECT @@IDENTITY Can retrieve the Identity of the last insertion.

...Ashok

"Devesh Aggarwal" <so*******@unieme.com> wrote in message
news:e7**************@TK2MSFTNGP09.phx.gbl...
Hi,

I have a backup and restore module in my project. The backup uses a typed dataset object (XSD) to get the data from database and creates a xml
file as
the backup file (using the WriteXml method of dataset). When doing the
restore i have to overwrite the data from xml back to database.

these are the steps that i follow.

1. get the data from database.
2. delete the data of step 1.
3. load the xml data.
4. transfer the xml data back on database.

The problem is the master tables have primary key as identity. How do i

get
the next identity for a table?
database has over 100 tables with the relation ship created.
all the above four action will happen in transaction.
XSD is created from server explorer in Visual studio.NET.
Database used is SQL 7.

Regards.
Devesh Aggarwal / Hafeez


Nov 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Aaron W. West | last post by:
Timings... sometimes there are almost too many ways to do the same thing. The only significant findings I see from all the below timings is: 1) Integer math is generally fastest, naturally....
2
by: Devesh Aggarwal | last post by:
Hi, I have a backup and restore module in my project. The backup uses a typed dataset object (XSD) to get the data from database and creates a xml file as the backup file (using the WriteXml...
6
by: Who.Really.Really.Cares | last post by:
Hi! I guess this must be a FAQ but I'll give it a try. I've searched the web and usenet archive and found only negative answers. But most of them were dated like 3-4 years back. Hasn't anything...
4
by: UDBDBA | last post by:
Hi: we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into this column manually and also let db2 generate a value for this column. Given a scenario, how can i find the NEXTVAL...
3
by: dusty | last post by:
Hi, I'll try to simplify the problem: I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work...
4
by: shorti | last post by:
Can anyone explain in greater (and more comprehensive) detail what the RESTART option does in the ALTER TABLE table ALTER COLUMN statement. This is the description in Info Center: RESTART or...
3
by: DavidB | last post by:
New to .net....sorry if this seems repetitive I have a dataset ordered by date (SQLDataAdapter SelectCommand uses Order By) and want to find a record by a UniqueID(Identity Column). Then I want...
5
by: Veeru71 | last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS IDENTITY), is there any way to get the last generated value by DB2 for the identity column? I can't use identity_val_local() as...
3
by: Rob | last post by:
Hi all, I have a bit of a complicated question, hope we have an SQL guru out there that can help us solve this killer problem. Due to the size of SQL Database we have (largest in the US), we...
15
by: gunnar.sigurjonsson | last post by:
I´m having some problem retrieving identity value from my newly inserted row into a view. I have two tables T1 and T2 which I define as following CREATE TABLE T1 ( id BIGINT GENERATED ALWAYS...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.