|
|
Posted by Erland Sommarskog on 12/12/06 22:37
Patti (pdavis269@worldsavings.com) writes:
> My original code does produce the correct result. Below I have
> simplified my original code and have given some sample data. I hope
> this helps clarifies what I am looking for. Thank you in advance.
You did not say which version of SQL Server you are using. The solution
below works on SQL 2000, but on SQL 2005 it should be possible to
all in one query.
First, add this column to @TempA:
rowno int IDENTITY
No you can insert all rows at once with:
INSERT dbo.tblInjectLoans (lnkey, AuditorID)
SELECT a.lnkey, a.auditorid
FROM @TempA a
JOIN @TempB b ON a.AuthorID = b.AuthorIS
WHERE b.lnkey >
a.rowno + (SELECT COUNT(il.Lnkey)
FROM tblInjectLoans il
WHERE il.AuditorID = a.AuditorID)
Since you did not include a repro script (i.e. the CREATE TABLE and
INSERT statements I was asking for) this untest.
Note that the code as you have written is not deterministic in which
loans that goes to which auditor, but nor is it anything that can be
called random.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|