"Alex" <alexdivascu@Xsbcglobal.net> wrote in message
news:UeNEc.10384$Qj6.8013@fed1read05...
> I must be missing something in this stored procedure. I tried to comment
my
> intentions, any help is appreciated....
>
> create procedure sp_load
> as
> BEGIN
> SET nocount on
> -- load the sys_load table initially
> TRUNCATE TABLE sys_load
> insert into dbo.sys_load (FirstName, LastName, Address, City, State, Zip,
> Phone, Prayer, DateEntered, Station)
> select "First Name", "Last Name", "Address", "City", "State", "Zip",
> "Phone", "Prayer", "Date Entered", "Station"
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\MyExcelog.xls',
> 'select * from [Excel Export File$]')
> -- see if a record already exists for the new Donor
> declare @intId int, @FirstName nvarchar(64), @LastName nvarchar(64),
> @Country nvarchar(64), @Email nvarchar(64)
> declare donor cursor for
> select a.intId, a.FirstName, a.LastName, (case when Country='Canada'
then
> 'TN' else 'TU' end) Country, a.Email
> from dbo.Donor a, dbo.sys_load b
> where a.FirstName = b.FirstName
> and a.LastName = b.LastName
> open donor
> fetch next from donor into @intId, @FirstName, @LastName, @Country,
@Email
> while (@@FETCH_STATUS!=-1)
> begin
> -- NO Donor Found with that FirstName, LastName
> if (@@FETCH_STATUS = -2)
> begin
> insert into dbo.Donor (FirstName, LastName, City, State, Zip, Phone,
> CompanyID, DateAdded)
> select a.FirstName, a.LastName, a.City, a.State, a.Zip, a.Phone, 1,
> getdate()
> from dbo.sys_load a
> end
> -- if donor record found, insert a new record into DonorDonations
> if (@@FETCH_STATUS = 0)
> begin
> insert into dbo.DonorDonations (intId, OrderTerms, CreditCardType,
> Amount, DateReceived, Source, Operator, LetterId, Status)
> values (@intId, 1, 0, 0, getdate(), @Country, @Email, @Country, 1)
> end
> fetch next from donor into @intId, @FirstName, @LastName, @Country,
@Email
> end
> close donor
> deallocate donor
> END
>
> Alex
>
>

"oj" <nospam_ojngo@home.com> wrote in message
news:%23Ed5sozXEHA.2940@TK2MSFTNGP09.phx.gbl...
> you can accomplish this without cursor.
>
> e.g.
>
> insert donor
> select *
> from sys_load sl
> where not exists(select *
> from donor d
> where d.firstname=sl.firstname
> and d.lastname=sl.lastname)
>
> insert donordonations
> select *
> from sys_load sl
> where exists(select *
> from donor d
> where d.firstname=sl.firstname
> and d.lastname=sl.lastname)
>
> "Alex" <alexdivascu@Xsbcglobal.net> wrote in message
> news:UeNEc.10384$Qj6.8013@fed1read05...
> > I must be missing something in this stored procedure. I tried to
comment
> my
> > intentions, any help is appreciated....
> >
> > create procedure sp_load
> > as
> > BEGIN
> > SET nocount on
> > -- load the sys_load table initially
> > TRUNCATE TABLE sys_load
> > insert into dbo.sys_load (FirstName, LastName, Address, City, State,
Zip,
> > Phone, Prayer, DateEntered, Station)
> > select "First Name", "Last Name", "Address", "City", "State", "Zip",
> > "Phone", "Prayer", "Date Entered", "Station"
> > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > 'Excel 8.0;Database=C:\MyExcelog.xls',
> > 'select * from [Excel Export File$]')
> > -- see if a record already exists for the new Donor
> > declare @intId int, @FirstName nvarchar(64), @LastName nvarchar(64),
> > @Country nvarchar(64), @Email nvarchar(64)
> > declare donor cursor for
> > select a.intId, a.FirstName, a.LastName, (case when Country='Canada'
> then
> > 'TN' else 'TU' end) Country, a.Email
> > from dbo.Donor a, dbo.sys_load b
> > where a.FirstName = b.FirstName
> > and a.LastName = b.LastName
> > open donor
> > fetch next from donor into @intId, @FirstName, @LastName, @Country,
> @Email
> > while (@@FETCH_STATUS!=-1)
> > begin
> > -- NO Donor Found with that FirstName, LastName
> > if (@@FETCH_STATUS = -2)
> > begin
> > insert into dbo.Donor (FirstName, LastName, City, State, Zip, Phone,
> > CompanyID, DateAdded)
> > select a.FirstName, a.LastName, a.City, a.State, a.Zip, a.Phone, 1,
> > getdate()
> > from dbo.sys_load a
> > end
> > -- if donor record found, insert a new record into DonorDonations
> > if (@@FETCH_STATUS = 0)
> > begin
> > insert into dbo.DonorDonations (intId, OrderTerms, CreditCardType,
> > Amount, DateReceived, Source, Operator, LetterId, Status)
> > values (@intId, 1, 0, 0, getdate(), @Country, @Email, @Country, 1)
> > end
> > fetch next from donor into @intId, @FirstName, @LastName, @Country,
> @Email
> > end
> > close donor
> > deallocate donor
> > END
> >
> > Alex
> >
> >
>
>

"Alex" <alexdivascu@Xsbcglobal.net> wrote in message
news:7ITEc.10397$Qj6.3164@fed1read05...
> Thanks Oj;
> If I wanted to do it via a cursor, can you let me know what I did wrong in
> this proc?
> For future reference...
> --Alex
>
>
> "oj" <nospam_ojngo@home.com> wrote in message
> news:%23Ed5sozXEHA.2940@TK2MSFTNGP09.phx.gbl...
> > you can accomplish this without cursor.
> >
> > e.g.
> >
> > insert donor
> > select *
> > from sys_load sl
> > where not exists(select *
> > from donor d
> > where d.firstname=sl.firstname
> > and d.lastname=sl.lastname)
> >
> > insert donordonations
> > select *
> > from sys_load sl
> > where exists(select *
> > from donor d
> > where d.firstname=sl.firstname
> > and d.lastname=sl.lastname)
> >
> > "Alex" <alexdivascu@Xsbcglobal.net> wrote in message
> > news:UeNEc.10384$Qj6.8013@fed1read05...
> > > I must be missing something in this stored procedure. I tried to
> comment
> > my
> > > intentions, any help is appreciated....
> > >
> > > create procedure sp_load
> > > as
> > > BEGIN
> > > SET nocount on
> > > -- load the sys_load table initially
> > > TRUNCATE TABLE sys_load
> > > insert into dbo.sys_load (FirstName, LastName, Address, City, State,
> Zip,
> > > Phone, Prayer, DateEntered, Station)
> > > select "First Name", "Last Name", "Address", "City", "State", "Zip",
> > > "Phone", "Prayer", "Date Entered", "Station"
> > > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > > 'Excel 8.0;Database=C:\MyExcelog.xls',
> > > 'select * from [Excel Export File$]')
> > > -- see if a record already exists for the new Donor
> > > declare @intId int, @FirstName nvarchar(64), @LastName nvarchar(64),
> > > @Country nvarchar(64), @Email nvarchar(64)
> > > declare donor cursor for
> > > select a.intId, a.FirstName, a.LastName, (case when Country='Canada'
> > then
> > > 'TN' else 'TU' end) Country, a.Email
> > > from dbo.Donor a, dbo.sys_load b
> > > where a.FirstName = b.FirstName
> > > and a.LastName = b.LastName
> > > open donor
> > > fetch next from donor into @intId, @FirstName, @LastName, @Country,
> > @Email
> > > while (@@FETCH_STATUS!=-1)
> > > begin
> > > -- NO Donor Found with that FirstName, LastName
> > > if (@@FETCH_STATUS = -2)
> > > begin
> > > insert into dbo.Donor (FirstName, LastName, City, State, Zip, Phone,
> > > CompanyID, DateAdded)
> > > select a.FirstName, a.LastName, a.City, a.State, a.Zip, a.Phone, 1,
> > > getdate()
> > > from dbo.sys_load a
> > > end
> > > -- if donor record found, insert a new record into DonorDonations
> > > if (@@FETCH_STATUS = 0)
> > > begin
> > > insert into dbo.DonorDonations (intId, OrderTerms, CreditCardType,
> > > Amount, DateReceived, Source, Operator, LetterId, Status)
> > > values (@intId, 1, 0, 0, getdate(), @Country, @Email, @Country, 1)
> > > end
> > > fetch next from donor into @intId, @FirstName, @LastName, @Country,
> > @Email
> > > end
> > > close donor
> > > deallocate donor
> > > END
> > >
> > > Alex
> > >
> > >
> >
> >
>
>

"oj" <nospam_ojngo@home.com> wrote in message
news:%23Svgu1$XEHA.2908@TK2MSFTNGP10.phx.gbl...
> Well, the cursor is defined on a set of rows where they both exists in
donor and
> sys_load table. The cursor will not give you the rows in sys_load that do
not
> exist in donor table. Thus, you can't insert into donor for these rows.
>
> If you want to do the cursor route, you would do something like this.
>
> declare cc cursor forward_only
> for select * from sys_load
>
> declare @a sysname, @b sysname
> open cc
> fetch next from cc into @a,@b
> while @@fetch_status=0
> begin
> --insert into donor
> if not exists(select * from donor where a=@a and b=@b)
> insert into donor(a,b)
> select @a,@b
>
> --insert into donations
> --this is always true because @a & @b exists in donor!
> insert into donations(a,b)
> select @a,@b
>
> fetch next from cc into @a,@b
> end
> close cc
> deallocate cc
>
>
>
> "Alex" <alexdivascu@Xsbcglobal.net> wrote in message
> news:7ITEc.10397$Qj6.3164@fed1read05...
> > Thanks Oj;
> > If I wanted to do it via a cursor, can you let me know what I did wrong
in
> > this proc?
> > For future reference...
> > --Alex
> >
> >
> > "oj" <nospam_ojngo@home.com> wrote in message
> > news:%23Ed5sozXEHA.2940@TK2MSFTNGP09.phx.gbl...
> > > you can accomplish this without cursor.
> > >
> > > e.g.
> > >
> > > insert donor
> > > select *
> > > from sys_load sl
> > > where not exists(select *
> > > from donor d
> > > where d.firstname=sl.firstname
> > > and d.lastname=sl.lastname)
> > >
> > > insert donordonations
> > > select *
> > > from sys_load sl
> > > where exists(select *
> > > from donor d
> > > where d.firstname=sl.firstname
> > > and d.lastname=sl.lastname)
> > >
> > > "Alex" <alexdivascu@Xsbcglobal.net> wrote in message
> > > news:UeNEc.10384$Qj6.8013@fed1read05...
> > > > I must be missing something in this stored procedure. I tried to
> > comment
> > > my
> > > > intentions, any help is appreciated....
> > > >
> > > > create procedure sp_load
> > > > as
> > > > BEGIN
> > > > SET nocount on
> > > > -- load the sys_load table initially
> > > > TRUNCATE TABLE sys_load
> > > > insert into dbo.sys_load (FirstName, LastName, Address, City, State,
> > Zip,
> > > > Phone, Prayer, DateEntered, Station)
> > > > select "First Name", "Last Name", "Address", "City", "State", "Zip",
> > > > "Phone", "Prayer", "Date Entered", "Station"
> > > > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > > > 'Excel 8.0;Database=C:\MyExcelog.xls',
> > > > 'select * from [Excel Export File$]')
> > > > -- see if a record already exists for the new Donor
> > > > declare @intId int, @FirstName nvarchar(64), @LastName
nvarchar(64),
> > > > @Country nvarchar(64), @Email nvarchar(64)
> > > > declare donor cursor for
> > > > select a.intId, a.FirstName, a.LastName, (case when
Country='Canada'
> > > then
> > > > 'TN' else 'TU' end) Country, a.Email
> > > > from dbo.Donor a, dbo.sys_load b
> > > > where a.FirstName = b.FirstName
> > > > and a.LastName = b.LastName
> > > > open donor
> > > > fetch next from donor into @intId, @FirstName, @LastName, @Country,
> > > @Email
> > > > while (@@FETCH_STATUS!=-1)
> > > > begin
> > > > -- NO Donor Found with that FirstName, LastName
> > > > if (@@FETCH_STATUS = -2)
> > > > begin
> > > > insert into dbo.Donor (FirstName, LastName, City, State, Zip,
Phone,
> > > > CompanyID, DateAdded)
> > > > select a.FirstName, a.LastName, a.City, a.State, a.Zip, a.Phone,
1,
> > > > getdate()
> > > > from dbo.sys_load a
> > > > end
> > > > -- if donor record found, insert a new record into DonorDonations
> > > > if (@@FETCH_STATUS = 0)
> > > > begin
> > > > insert into dbo.DonorDonations (intId, OrderTerms,
CreditCardType,
> > > > Amount, DateReceived, Source, Operator, LetterId, Status)
> > > > values (@intId, 1, 0, 0, getdate(), @Country, @Email, @Country, 1)
> > > > end
> > > > fetch next from donor into @intId, @FirstName, @LastName,
@Country,
> > > @Email
> > > > end
> > > > close donor
> > > > deallocate donor
> > > > END
> > > >
> > > > Alex
> > > >
> > > >
> > >
> > >
> >
> >
>
>