Re: Cursor vs. table data type

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: dw (cougarmana_NOSPAM_at_uncw.edu)
Date: 07/08/04 

Date: Thu, 8 Jul 2004 13:32:40 -0400

Thanks, Tom. Here are the specs for what I'm trying to do, with table ddl's,

CREATE TABLE [tblCourseInfo] (
 [pk] [int] IDENTITY (1, 1) NOT NULL ,
 [courseSection] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [semester] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [collegeLevel] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [FAC_ID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [LAST_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [FIRST_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [MID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_tblCourseInfo] PRIMARY KEY CLUSTERED
 (
  [pk]
) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [tblCourseSect] (
 [CourseSection] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [Semester] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [InstPIDM] [int] NULL ,
 [CourseName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [collegeLevel] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_tblCourseSect] PRIMARY KEY CLUSTERED
 (
  [CourseSection],
  [Semester]
 ) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [tblGeneralPerson] (
 [pidm] [int] IDENTITY (1, 1) NOT NULL ,
 [id] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [firstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [middleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [lastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [dateLastMod] [smalldatetime] NULL CONSTRAINT
[DF_tblGeneralPerson_dateLastMod] DEFAULT (getdate()),
 [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblGeneralPerson_dateAdded]
DEFAULT (getdate()),
 CONSTRAINT [PK_tblGeneralPerson] PRIMARY KEY CLUSTERED
 (
  [pidm]
) ON [PRIMARY]
 ) ON [PRIMARY]
GO

Here are the specs:
    - For each record in tblCourseInfo:
        - If there's a record in tblCourseSect on that tblCourseInfo's term
and crs_no:
            - If tblCourseInfo.fac_id is numeric and tblCourseSect.instPIDM
is null:
                - If the fac_id doesn't exist in tblGeneralPerson.id, insert
a new record into that table using the tblCourseInfo.last_name,
tblcourseInfo.first_name, and get back the PIDM; insert pidm and some other
fields from tblCourseInfo into tblCourseSect
                - If the fac_id exists in tblGeneralPerson.id, see if its
PIDM matches tblCourseSect.instPIDM; if doesn't, update
tblCourseSect.instPIDM with PIDM from tblGeneralPerson
        - Else if there's no record in tblCourseSect on the tblCourseInfo
term and crs_no:
            - If tblCourseInfo.fac_id is blank, insert a record into
tblCourseSect w/out the instPIDM, using fields from tblCourseInfo
            - Else if tblCourseInfo.fac_id isn't blank, search the
tblGeneralPerson on tblCourseInfo.fac_id (tblGeneralPerson.id);
                - If id is NOT in tblGeneralPerson, add the person to that
table and get back the PIDM
                - Else if id is in tblGeneralPerson, get back the PIDM and
insert a record into tblCourseSect

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eRQ8V2QZEHA.556@tk2msftngp13.phx.gbl...
> Cursors are extremely slow. What exactly do you need to do? Are you
> looking to insert rows into a table that don't already exist?
>
> --
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
>
> "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message
> news:u7e6LgOZEHA.2844@TK2MSFTNGP12.phx.gbl...
> Hello, all. We need to loop through the values in one table and compare
them
> with values in another. Which is more efficient -- a cursor or a table
data
> type for the first table? For example, this is the sort of query we run,
>
> -- from the cursor on the 1st table, we get @strCourseSection and
> @strSemester
> Select @intInstPIDM = a.instPIDM From dbo.tblCourseSect a
> Where a.courseSection = @strCourseSection and a.semester =
@strSemester
> If (@intInstPIDM Is Not Null) Begin ........
> -- do an insert/update on second table here
>
> Thanks.
>
>

Relevant Pages

  • Re: Cursor vs. table data type
    ... Sorry for the paltry specs. ... CONSTRAINT PRIMARY KEY CLUSTERED ... CREATE TABLE [tblCourseSect] ( ... and get back the PIDM; ...
    (microsoft.public.sqlserver.programming)
  • Re: Cursor vs. table data type
    ... Columnist, SQL Server Professional ... CREATE TABLE [tblGeneralPerson] ( ... If there's a record in tblCourseSect on that tblCourseInfo's term ... and get back the PIDM; ...
    (microsoft.public.sqlserver.programming)