How to get the @@Identity for multiple Inserts?

Hi All,

Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @.@.Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.

Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users

INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@.@.Identity,'SomeOtherValue')

Thanks
Tanveerrewrite to a cursor insert
or
after the hughe insert, determine which are new and update/insert the depending table accordingly.
I'm not entirely sure what you mean by 'this situation' though.|||A better solution would be to create a temporary table .. lets say UserConsolidated like
(Userid,FirstName, SecondName,OtherFields) and then use this table to get data into User and UserDependent.|||At least for now, the @.@.identity values are processed as a block, in other words if you have five different inserts going (on different spids) at the same time, each block of the five will have a contiguous range of @.@.identity values. This means that if you insert 30 rows, the @.@.identity value will be for the first row, and @.@.identity+29 will be the value for the last row in your insert.

Note that this will change at some point in time, probably in the release after Yukon.

-PatP

how to get latest record from a table?

hi guys.
i have a table that store all transactions. In this table, a member can has multiple records, there is a field to store the date.
1. the problem i'm facing is, i need to retrieve only the latest record. for example, the transaction table contains of 20 records for 5 members, i need to retrieve the latest record for 5 members.
2. another problem that i face is, how can i make comparison with the date. let say i want to retrieve the record that more than 90 days?

thanks for all advises.

Engloon:

For #1 you need to make sure that you have an index on "member id" so that you can efficiently retrieve records based on the "member id."

For #2 you need to have an index baded on the date and you need to make sure that your field is has a "datetime" datatype.

declare @.member table
( memberId integer,
tranDT datetime
)
insert into @.member
select 5, '1/9/6' union all
select 5, '5/8/6' union all
select 5, '12/15/6' union all
select 5, '3/12/7'
--select * from @.member

/*
memberId tranDT
-- --
5 2006-01-09 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-12-15 00:00:00.000
5 2006-03-12 00:00:00.000
*/

select top 1
memberId,
tranDt
from @.member
order by tranDt desc

/*
memberId tranDt
-- --
5 2006-12-15 00:00:00.000
*/

select memberId,
tranDt
from @.member
where tranDt < getdate() - 90
order by tranDt desc

/*
memberId tranDt
--
5 2006-12-15 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-01-09 00:00:00.000
*/

|||

1. This should give you an idea about how to get the latest entry for each member. (I assume you have a MemberID column.)

Code Snippet

SELECT
MemberID,
max( TransactionDate )
FROM MyTable
GROUP BY MemberID
ORDER BY MemberID

2. Add a WHERE clause, something like this:

Code Snippet

WHERE TransactionDate < ( dateadd( day, -90, getdate() ))

|||many thanks to Arnie Rowland and Kent Waldrop. I appreciate your help.
but the @.member table contains not only 1 member. I'm thinking to use distinct function to get only 1 record for each member.
Will let you know if I can solve the problem.

|||here is the result of my sql query
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
8 18-Sep-2005
8 18-Sep-2005
187 16-May-2006
187 14-May-2006
187 06-Jun-2006
187 29-Jul-2005
187 05-Jan-2007
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
303 19-Dec-2006
306 03-Oct-2005
306 16-Dec-2005

the result that i desire would be like this
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
187 16-May-2006
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
306 03-Oct-2005
|||

Use the following query...

Code Snippet

Select Identity(Int,1,1) as UID,Id,Date Into #Temp from Members;

Select Mem.Id,Mem.Date From #Temp Mem
Join (Select Min(UId) UID,Id from #Temp Group By Id) as LastRec On LastRec.UID = Mem.UID;

Drop Table #Temp;

|||

Strange, it 'sounds' like you are 'blowing off' the solution that I provided. Too bad, because using the data you posted, and the query I provided, the exact resultset you asked for is produced.

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
TransactionDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005' )

SELECT
MembershipID,
TransactionDate = max( TransactionDate )
FROM @.MyTable
GROUP BY MembershipID
ORDER BY MembershipID

MembershipID TransactionDate
3 2007-01-03 00:00:00.000
8 2006-08-05 00:00:00.000
187 2007-01-05 00:00:00.000
195 2006-03-14 00:00:00.000
239 2005-08-29 00:00:00.000
275 2005-08-07 00:00:00.000
303 2006-12-19 00:00:00.000
306 2005-12-16 00:00:00.000

|||

But your query doesn't give the last row, it gives the max tran date. He wants the row with the max tran date:

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
Date datetime,
someOtherColumn int default (100),
yetAnotherColumn int default (100)
)

SET NOCOUNT ON

INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '16-Dec-2005' )

select membershipId, date, someOtherColumn, yetAnotherColumn
from (select membershipId, date, someOtherColumn, yetAnotherColumn,
row_number() over (partition by membershipId order by date desc) as rowNum
from @.MyTable) as myTable
where rowNum = 1

|||

You are right Louis -but in defense, I was working with the data he provided, assuming that we were working with the 'first step'. (It's so much easier when folks provide us with an adequate explanition and sample data...)

The query that I provided should be used as a derived table and then would provide the requested data -and it works in both SQL 2000 and SQL 2005.

(However, there is one significant issue -how to deal with duplicate MembershipID/Date records -and of course, your proposed solution has that same problem).

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
[Date] datetime,
MyOtherColumn int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007', 1 )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006', 2 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 3 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 4 )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006', 5 )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006', 6 )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006', 7 )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005', 8 )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007', 9 )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006', 10 )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005', 11 )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005', 12 )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005', 13 )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006', 14 )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005', 15 )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005', 17 )

SELECT
t.MembershipID,
t.[Date],
t.MyOtherColumn
FROM @.MyTable t
JOIN ( SELECT
MembershipID,
[Date] = max( [Date] )
FROM @.MyTable
GROUP BY MembershipID
) dt
ON ( t.MembershipID = dt.MembershipID
AND t.[Date] = dt.[Date]
)
ORDER BY t.MembershipID

MembershipID Date MyOtherColumn
-
3 2007-01-03 00:00:00.000 1
8 2006-08-05 00:00:00.000 2
187 2007-01-05 00:00:00.000 9
195 2006-03-14 00:00:00.000 10
239 2005-08-29 00:00:00.000 11
275 2005-08-07 00:00:00.000 12
303 2006-12-19 00:00:00.000 14
306 2005-12-16 00:00:00.000 17

|||i'm so sorry, i should give an example of the result i need in the begining.
and many thanks to Arnie and all programmers who tried to help me.
i really appreciate it.

How to get last lsn for my DB

I need to restore multiple set file from a transaction log backup.
To know from which position of this file starting my restore, i need to know
the last lsn point restored on my DB.
while @.id_start < @.id_stop
begin
set @.id_start = @.id_start + 1
RESTORE LOG [MYDB]
FROM DISK = N'Y:\MYDB_LOG'
WITH STANDBY = 'C:\Programmi\Microsoft SQL Server\MSSQL\UNDO_MYDB.ldf',
FILE = @.id_start
if @.@.error > 0
begin
set @.id_stop = 0
BREAK
end
end
I need to know the @.id_start to begin the restore for all backup set missing
.
ThanksHi
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 3, recovery
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:7BF739FD-E00E-4C13-ABDF-75CC564F31CD@.microsoft.com...
> I need to restore multiple set file from a transaction log backup.
> To know from which position of this file starting my restore, i need to
know
> the last lsn point restored on my DB.
>
> while @.id_start < @.id_stop
> begin
> set @.id_start = @.id_start + 1
> RESTORE LOG [MYDB]
> FROM DISK = N'Y:\MYDB_LOG'
> WITH STANDBY = 'C:\Programmi\Microsoft SQL
Server\MSSQL\UNDO_MYDB.ldf',
> FILE = @.id_start
> if @.@.error > 0
> begin
> set @.id_stop = 0
> BREAK
> end
> end
> I need to know the @.id_start to begin the restore for all backup set
missing.
> Thanks

How to get hierarchical xml based of multiple tables xml columns

Hi,
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..