Finding identity column value from a table

[Devesh Aggarwal]

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

[G.Ashok]

Re: Finding identity column value from a table

SELECT @@IDENTITY Can retrieve the Identity of the last insertion.

....Ashok

"Devesh Aggarwal" <solutions@unie me.com> wrote in message
news:e7zczZ8lDH A.1284@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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[/color]
as[color=blue]
> 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[/color]
get[color=blue]
> 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
>
>[/color]

[Devesh Aggarwal]

Re: Finding identity column value from a table

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" <gw2ksoft@hotma il.com> wrote in message
news:%23AyuOklm DHA.2068@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> SELECT @@IDENTITY Can retrieve the Identity of the last insertion.
>
> ...Ashok
>
> "Devesh Aggarwal" <solutions@unie me.com> wrote in message
> news:e7zczZ8lDH A.1284@TK2MSFTN GP09.phx.gbl...[color=green]
> > Hi,
> >
> > I have a backup and restore module in my project. The backup uses a[/color][/color]
typed[color=blue][color=green]
> > dataset object (XSD) to get the data from database and creates a xml[/color][/color]
file[color=blue]
> as[color=green]
> > 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[/color]
> get[color=green]
> > 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
> >
> >[/color]
>
>[/color]