Master/Detail into temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Master/Detail into temp table

Author

Topic

stuppi
Starting Member

2 Posts

Posted - 2008-09-23 : 14:23:16

I have a simple master/detail relationship between two tables based on a primary and foreign key:


CREATE TABLE Author
ID int IDENTITY(1,1) NOT NULL,
Name varchar(128) NOT NULL,
Age int NOT NULL

CREATE TABLE Title
AUTHORID int NOT NULL,
Title varchar(128) NOT NULL

The requirement is to create a grid that shows three colunms for each record in Author:

Name Age Titles
------- ------- -----------
Author.Name Author.Age Title.Title1, Title.Title2, Title.Title3

I'm currently using a stored proc containing 2 cursors creating a temp table for results. This is pseudo-code:

1. declare Titles
2. create table #result
3. insert into #result select Name, Age, null as Titles from Author
4. cursor Author for select ID from Author
5. cursor Title for select AUTHORID, Title from Title
6. while cursor Author fetch
while cursor Title fetch
Titles = Titles + Title + ','
fetch next Title
end
update #results set Titles = Titles
fetch next Author
end

This all works well but I understand cursors can be slow. My question is can this result set be generated using a set based approach without using cursors?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 14:27:21

[code]SELECT a.Name,
a.Age,
LEFT(tl.titlelist,LEN(tl.titlelist)-1) AS Titles
FROM Author a
CROSS APPLY(SELECT Title+',' AS [text()]
FROM Title
WHERE AUTHORID=a.ID
FOR XML PATH(''))tl(titlelist)[/code]