|
Execute the following
Microsoft SQL Server T-SQL script in Management Studio Query Editor to create a junction table (in a table variable) to reflect the many-to-many relationship between book authors and book titles.
-- SQL Server nested cursors - T-SQL table variable
-- SQL junction table - many-to-many relationship
USE pubs;
SET nocount ON
DECLARE @BookTitle VARCHAR(10),
@AuthorID VARCHAR(25)
DECLARE @Result TABLE(
Title VARCHAR(100),
Author VARCHAR(20)
)
DECLARE curBookTitle CURSOR FOR
SELECT title_id
FROM titles
ORDER BY title_id
OPEN curBookTitle
FETCH curBookTitle
INTO @BookTitle
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'OUTER CURSOR LOOP'
DECLARE curTitleAuthor CURSOR FOR
SELECT au_id
FROM titleauthor
WHERE title_id = @BookTitle
OPEN curTitleAuthor
FETCH curTitleAuthor
INTO @AuthorID
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT ' INNER CURSOR LOOP'
INSERT @Result
SELECT @BookTitle,
@AuthorID
FETCH curTitleAuthor
INTO @AuthorID
END -- while
CLOSE curTitleAuthor
DEALLOCATE curTitleAuthor
FETCH curBookTitle
INTO @BookTitle
END -- while
CLOSE curBookTitle
DEALLOCATE curBookTitle
SELECT *
FROM @Result
GO
/* Partial results
Title Author
BU1032 213-46-8915
BU1032 409-56-7008
BU1111 267-41-2394
BU1111 724-80-9391
BU2075 213-46-8915
BU7832 274-80-9391
MC2222 712-45-1867
*/
/* Partial listing from Messages
OUTER CURSOR LOOP
INNER CURSOR LOOP
INNER CURSOR LOOP
OUTER CURSOR LOOP
INNER CURSOR LOOP
INNER CURSOR LOOP
OUTER CURSOR LOOP
INNER CURSOR LOOP
OUTER CURSOR LOOP
*/
|
