DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to build a junction table with nested cursors?

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

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE