Cursor Update problem

I am using a cursor to take information from a temp table and either insert
or update another table. I am using a while loop to fetch all the vaules
from the cursor and then I close and deallocate the cursor. Everything runs
fine the first time but when I run the procedure again it updates the first
record with the information from the last record process from the time
before. If it helps I am running it as a job just like it will run when
completed. Ideas on why I am getting data from the previous run?Can you poste the code instead just a brief description of the problem?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Shannon Thompson" wrote:

> I am using a cursor to take information from a temp table and either inser
t
> or update another table. I am using a while loop to fetch all the vaules
> from the cursor and then I close and deallocate the cursor. Everything ru
ns
> fine the first time but when I run the procedure again it updates the firs
t
> record with the information from the last record process from the time
> before. If it helps I am running it as a job just like it will run when
> completed. Ideas on why I am getting data from the previous run?|||Here is my code but I cannot give you the code for stored procedures call
from this code here because they are Encrypted and part of the software
program I am integrating with.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xxx]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE procedure xxxx
AS
/*
** Declare & initialize Local Variables
***
*/
DECLARE @.iReturnCode int,
@.iRet int,
@.vchFirstName nvarchar(255) ,
@.vchLastName nvarchar(255) ,
@.vchAdSource nvarchar(255) ,
@.vchOnyxCode nvarchar(255) ,
@.vchAddress1 nvarchar(255) ,
@.vchCity nvarchar(255) ,
@.chStateCode nvarchar(50) ,
@.vchPostCode nvarchar(40) ,
@.chCountryCode nvarchar(50) ,
@.vchPhoneType nvarchar(50) ,
@.vchPhoneNumber nvarchar(40) ,
@.vchBestTime nvarchar(255),
@.vchEmail nvarchar(255),
@.iHt_Feet nvarchar(50),
@.iHt_Inches nvarchar(50),
@.iWeight nvarchar(50),
@.dtDOB nvarchar(50),
@.vchInsurance nvarchar(255),
@.vchOtherIns nvarchar(255),
@.vchInsuranceType nvarchar(255),
@.vchSem nvarchar(255),
@.vchSemSrc nvarchar(255),
@.dtTimeStamp nvarchar(255),
@.iIndividualId int ,
@.iIncidentId int,
@.onyx_cursor cursor,
@.chInsUpd nchar(1),
@.iPhoneTypeId int,
@.getDate datetime,
@.dtPreviousUpdateDate datetime,
@.iHeight int,
@.dtUpdate datetime,
@.bmi float
set @.iReturnCode = 0
set @.iRet = 0
set @.getDate = getDate()
set @.bmi = 0
set @.onyx_cursor = cursor
--local Scroll Keyset Optimistic
FOR Select
vchFirstName,
vchLastName,
vchAdSource,
vchOnyxCode,
vchAddress1,
vchCity,
chStateCode,
vchPostCode,
chCountryCode,
vchPhoneType,
vchPhoneNumber,
vchBestTime,
vchEmail,
iHt_Feet,
iHt_Inches,
iWeight,
dtDOB,
vchInsurance,
vchOtherIns,
vchInsuranceType,
vchSem,
vchSemSrc,
dtTimeStamp
from CallCenter_Temp
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
-- loop while there are still records in table
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
(convert(float,ROUND(@.iHt_Feet,0))*12))
IF @.iHeight <> 0 AND @.iWeight <> 0
BEGIN
declare @.meters float,
@.totalinches float,
@.kilos float,
@.metersq float
set @.totalinches = convert(float,@.iHeight)
set @.meters = @.totalinches/39.36
set @.kilos = convert(float,@.iWeight)/2.2
set @.metersq = @.meters * @.meters
set @.bmi = Round(@.kilos/@.metersq,0)
END
set @.vchFirstName = UPPER(@.vchFirstName)
set @.vchLastName = UPPER(@.vchLastName)
set @.vchAddress1 = UPPER(@.vchAddress1)
set @.vchCity = UPPER(@.vchCity)
set @.chStateCode = UPPER(@.chStateCode)
set @.chCountryCode = UPPER(@.chCountryCode)
-- determine phone type
SELECT @.iPhoneTypeId =
CASE LOWER(RTRIM(@.vchPhoneType))
WHEN 'home' THEN 119
WHEN 'cell' THEN 103
WHEN 'work' THEN 102
ELSE 119
END
-- if no Onyx Id is listed then search for the individual
IF @.iIndividualId is null
BEGIN
-- check to see if Person is in Onyx
exec @.iRet = wbocpscOnyxTalley
@.vchFirstName,
@.vchLastName,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
NULL
if (@.iRet <> 0)
begin
--update
set @.chInsUpd = 'U'
set @.iIndividualId = @.iRet
end
else
begin
--insert
set @.chInsUpd = 'I'
end
end
-- iIndividualId is given so this is an update
ELSE
BEGIN
set @.chInsUpd = 'U'
END
print @.chInsUpd + ' ' + @.vchLastName
if @.chInsUpd = 'I'
begin
exec @.iReturnCode = wbospsiIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
119,
null,
null,
1,
1,
0,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1,
1
if @.iReturnCode <> 0
begin
print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
ELSE
begin
select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = ospsgCheckRecordLock
@.dtUpdate OUTPUT,
@.dtPreviousUpdateDate,
1
-- if error returned then must change dtUpdateDate to current
if (@.iReturnCode <> 0)
begin
UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
iIndividualId = @.iIndividualId
exec @.iReturnCode = wbospsuIndividual
1,
@.iIndividualId,
'ENG',
'PatientLC',
null,
@.vchFirstName,
null,
@.vchLastName,
null,
@.vchAddress1,
null,
null,
@.vchCity,
@.chStateCode,
@.chCountryCode,
@.vchPostCode,
@.vchPhoneNumber,
@.vchEmail,
'',
null,
null,
null,
0,
'',
'',
'',
null,
null,
@.iPhoneTypeId,
null,
null,
null,
1,
1,
0,
null,
null,
null,
@.iHeight,
@.iWeight,
@.bmi,
null,
null,
@.dtDOB,
null,
'CCLeads',
@.getDate,
0,
1
if @.iReturnCode <> 0
begin
print 'update failed for ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
else
begin
print 'updated ' + @.vchFirstName + ' ' + @.vchLastName
print @.iReturnCode
end
end
end
--fetch next record
FETCH NEXT from @.onyx_cursor into
@.vchFirstName,
@.vchLastName,
@.vchAdSource,
@.vchOnyxCode,
@.vchAddress1,
@.vchCity,
@.chStateCode,
@.vchPostCode,
@.chCountryCode,
@.vchPhoneType,
@.vchPhoneNumber,
@.vchBestTime,
@.vchEmail,
@.iHt_Feet,
@.iHt_Inches,
@.iWeight,
@.dtDOB,
@.vchInsurance,
@.vchOtherIns,
@.vchInsuranceType,
@.vchSem,
@.vchSemSrc,
@.dtTimeStamp
END
CLOSE @.onyx_cursor
DEALLOCATE @.onyx_cursor
return @.iReturnCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Can you poste the code instead just a brief description of the problem?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Shannon Thompson" wrote:
>|||Shannon,
The cursor is based on a permanent table. How are you feeding this table
before calling the sp?
AMB
"Shannon Thompson" wrote:

> Here is my code but I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[xxx]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE procedure xxxx
> AS
> /*
> ** Declare & initialize Local Variables
> ***
> */
>
> DECLARE @.iReturnCode int,
> @.iRet int,
> @.vchFirstName nvarchar(255) ,
> @.vchLastName nvarchar(255) ,
> @.vchAdSource nvarchar(255) ,
> @.vchOnyxCode nvarchar(255) ,
> @.vchAddress1 nvarchar(255) ,
> @.vchCity nvarchar(255) ,
> @.chStateCode nvarchar(50) ,
> @.vchPostCode nvarchar(40) ,
> @.chCountryCode nvarchar(50) ,
> @.vchPhoneType nvarchar(50) ,
> @.vchPhoneNumber nvarchar(40) ,
> @.vchBestTime nvarchar(255),
> @.vchEmail nvarchar(255),
> @.iHt_Feet nvarchar(50),
> @.iHt_Inches nvarchar(50),
> @.iWeight nvarchar(50),
> @.dtDOB nvarchar(50),
> @.vchInsurance nvarchar(255),
> @.vchOtherIns nvarchar(255),
> @.vchInsuranceType nvarchar(255),
> @.vchSem nvarchar(255),
> @.vchSemSrc nvarchar(255),
> @.dtTimeStamp nvarchar(255),
> @.iIndividualId int ,
> @.iIncidentId int,
> @.onyx_cursor cursor,
> @.chInsUpd nchar(1),
> @.iPhoneTypeId int,
> @.getDate datetime,
> @.dtPreviousUpdateDate datetime,
> @.iHeight int,
> @.dtUpdate datetime,
> @.bmi float
> set @.iReturnCode = 0
> set @.iRet = 0
> set @.getDate = getDate()
> set @.bmi = 0
> set @.onyx_cursor = cursor
> --local Scroll Keyset Optimistic
> FOR Select
> vchFirstName,
> vchLastName,
> vchAdSource,
> vchOnyxCode,
> vchAddress1,
> vchCity,
> chStateCode,
> vchPostCode,
> chCountryCode,
> vchPhoneType,
> vchPhoneNumber,
> vchBestTime,
> vchEmail,
> iHt_Feet,
> iHt_Inches,
> iWeight,
> dtDOB,
> vchInsurance,
> vchOtherIns,
> vchInsuranceType,
> vchSem,
> vchSemSrc,
> dtTimeStamp
> from CallCenter_Temp
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into
> @.vchFirstName,
> @.vchLastName,
> @.vchAdSource,
> @.vchOnyxCode,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> @.chCountryCode,
> @.vchPhoneType,
> @.vchPhoneNumber,
> @.vchBestTime,
> @.vchEmail,
> @.iHt_Feet,
> @.iHt_Inches,
> @.iWeight,
> @.dtDOB,
> @.vchInsurance,
> @.vchOtherIns,
> @.vchInsuranceType,
> @.vchSem,
> @.vchSemSrc,
> @.dtTimeStamp
> -- loop while there are still records in table
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.iHeight= convert(int,convert(float,ROUND(@.iHt_Inc
hes,0)) +
> (convert(float,ROUND(@.iHt_Feet,0))*12))
> IF @.iHeight <> 0 AND @.iWeight <> 0
> BEGIN
> declare @.meters float,
> @.totalinches float,
> @.kilos float,
> @.metersq float
> set @.totalinches = convert(float,@.iHeight)
> set @.meters = @.totalinches/39.36
> set @.kilos = convert(float,@.iWeight)/2.2
> set @.metersq = @.meters * @.meters
> set @.bmi = Round(@.kilos/@.metersq,0)
> END
> set @.vchFirstName = UPPER(@.vchFirstName)
> set @.vchLastName = UPPER(@.vchLastName)
> set @.vchAddress1 = UPPER(@.vchAddress1)
> set @.vchCity = UPPER(@.vchCity)
> set @.chStateCode = UPPER(@.chStateCode)
> set @.chCountryCode = UPPER(@.chCountryCode)
> -- determine phone type
> SELECT @.iPhoneTypeId =
> CASE LOWER(RTRIM(@.vchPhoneType))
> WHEN 'home' THEN 119
> WHEN 'cell' THEN 103
> WHEN 'work' THEN 102
> ELSE 119
> END
> -- if no Onyx Id is listed then search for the individual
> IF @.iIndividualId is null
> BEGIN
> -- check to see if Person is in Onyx
> exec @.iRet = wbocpscOnyxTalley
> @.vchFirstName,
> @.vchLastName,
> @.vchAddress1,
> @.vchCity,
> @.chStateCode,
> @.vchPostCode,
> NULL
> if (@.iRet <> 0)
> begin
> --update
> set @.chInsUpd = 'U'
> set @.iIndividualId = @.iRet
> end
> else
> begin
> --insert
> set @.chInsUpd = 'I'
> end
> end
> -- iIndividualId is given so this is an update
> ELSE
> BEGIN
> set @.chInsUpd = 'U'
> END
> print @.chInsUpd + ' ' + @.vchLastName
> if @.chInsUpd = 'I'
> begin
> exec @.iReturnCode = wbospsiIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> 119,
> null,
> null,
> 1,
> 1,
> 0,
> null,
> null,
> @.iHeight,
> @.iWeight,
> @.bmi,
> null,
> null,
> null,
> @.dtDOB,
> null,
> 'CCLeads',
> @.getDate,
> 0,
> 1,
> 1
> if @.iReturnCode <> 0
> begin
> print 'insert failed for ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> else
> begin
> print 'inserted ' + @.vchFirstName + ' ' + @.vchLastName
> print @.iReturnCode
> end
> end
> ELSE
> begin
> select @.dtPreviousUpdateDate = dtUpdateDate FROM Individual WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = ospsgCheckRecordLock
> @.dtUpdate OUTPUT,
> @.dtPreviousUpdateDate,
> 1
> -- if error returned then must change dtUpdateDate to current
> if (@.iReturnCode <> 0)
> begin
> UPDATE Individual SET dtUpdateDate = @.getDate,chUpdateBy='sa' WHERE
> iIndividualId = @.iIndividualId
> exec @.iReturnCode = wbospsuIndividual
> 1,
> @.iIndividualId,
> 'ENG',
> 'PatientLC',
> null,
> @.vchFirstName,
> null,
> @.vchLastName,
> null,
> @.vchAddress1,
> null,
> null,
> @.vchCity,
> @.chStateCode,
> @.chCountryCode,
> @.vchPostCode,
> @.vchPhoneNumber,
> @.vchEmail,
> '',
> null,
> null,
> null,
> 0,
> '',
> '',
> '',
> null,
> null,
> @.iPhoneTypeId,
> null,
> null,
> null,|||This table (CallCenter_temp) is populated by another stored procedure that
gets a file list from a directory, puts that into a true temp table (gets
created and deleted within procedure) and batch inserts each text file:
BEGIN
/*
** Declare & initialize Local Variables
*/
DECLARE
@.iReturnCode int,
@.MyFile varchar(200),
@.SQL varchar(2000),
@.Path varchar(400),
@.onyx_cursor cursor,
@.vchFileName varchar(255),
@.vchXPCMD nvarchar(255)
select
@.iReturnCode = 0
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
'CCFiles')
BEGIN
DROP TABLE CCFiles
END
CREATE TABLE [dbo].[CCFiles] (
[vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
IF @.iReturnCode = 0
BEGIN
SET @.onyx_cursor = cursor
FOR SELECT
vchFileName
FROM CCFiles
OPEN @.onyx_cursor
FETCH NEXT from @.onyx_cursor into @.vchFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
@.vchFileName + '"' +
' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
--SELECT @.SQL
EXECUTE (@.SQL)
IF @.iReturnCode = 0
BEGIN
set @.vchFileName = @.Path + @.vchFileName
set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
--execute master..xp_cmdshell @.vchXPCMD
END
FETCH NEXT from @.onyx_cursor into @.vchFileName
END
END
DROP TABLE CCFiles
return @.iReturnCode
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" wrote:
> Shannon,
> The cursor is based on a permanent table. How are you feeding this table
> before calling the sp?
>
> AMB
> "Shannon Thompson" wrote:
>|||> I cannot give you the code for stored procedures call
> from this code here because they are Encrypted and part of the software
> program I am integrating with.
Personally I'd want to decrypt those procs to see if it's feasible to
rewrite your code without a cursor.
http://www.planetsourcecode.com/vb/...6J00S003GU.html
David Portas
SQL Server MVP
--|||Shannon,
In the previous post you are not closing and deallocating the cursor and
this cursor.
AMB
"Shannon Thompson" wrote:
> This table (CallCenter_temp) is populated by another stored procedure that
> gets a file list from a directory, puts that into a true temp table (gets
> created and deleted within procedure) and batch inserts each text file:
> BEGIN
> /*
> ** Declare & initialize Local Variables
> */
> DECLARE
> @.iReturnCode int,
> @.MyFile varchar(200),
> @.SQL varchar(2000),
> @.Path varchar(400),
> @.onyx_cursor cursor,
> @.vchFileName varchar(255),
> @.vchXPCMD nvarchar(255)
> select
> @.iReturnCode = 0
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
> 'CCFiles')
> BEGIN
> DROP TABLE CCFiles
> END
> CREATE TABLE [dbo].[CCFiles] (
> [vchFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> SET @.Path = '\\wms1\c$\CallCenterWebSite\data'
> EXECUTE cpListFiles @.Path,'CCFiles','%.txt',NULL,0
> IF @.iReturnCode = 0
> BEGIN
> SET @.onyx_cursor = cursor
> FOR SELECT
> vchFileName
> FROM CCFiles
> OPEN @.onyx_cursor
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.SQL = 'BULK INSERT [Onyx]..CallCenter_temp FROM "' + @.Path +
> @.vchFileName + '"' +
> ' WITH(BATCHSIZE = 250 ,DATAFILETYPE = "char" ,FIELDTERMINATOR = "|"
> ,ROWTERMINATOR = "\n",MAXERRORS = 50 ,TABLOCK)'
> --SELECT @.SQL
> EXECUTE (@.SQL)
> IF @.iReturnCode = 0
> BEGIN
> set @.vchFileName = @.Path + @.vchFileName
> set @.vchXPCMD = '@.Del ' + RTrim(@.vchFileName)
> --execute master..xp_cmdshell @.vchXPCMD
> END
> FETCH NEXT from @.onyx_cursor into @.vchFileName
> END
> END
> DROP TABLE CCFiles
> return @.iReturnCode
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
> "Alejandro Mesa" wrote:
>|||The reason I use a cursor is that having limited SQL Stored Procedure
experience (mainly VBScript SQL experience) I know of no other way to go
record by record to call these stored procedures. I have de-crypted these
procedures but by using these stored procedures instead of recreating them I
can safety insert data into the database without breaking any middle tier
rules of the software and corrupt any of the data.
The store procedures are made to put one person at a time into the database
(when a users clicks save on the web page) not bulk so this is why I am usin
g
the cursor.
"David Portas" wrote:

> Personally I'd want to decrypt those procs to see if it's feasible to
> rewrite your code without a cursor.
> http://www.planetsourcecode.com/vb/...6J00S003GU.html
> --
> David Portas
> SQL Server MVP
> --
>|||Why did you destroy Standard SQL behavior? Why are there more NULLs in
one table than should be in an entire Fortune 500 accounting package?
Do you really have a lot of data elements that are in Chinese and 255
characters long? Why do you have data type prefixes on variable names,
which is a violation of both good programming and ISO-11179? Why do you
have numeric data elements in strings? What kind of total garbage are
trying to get with things like "weight VARCHAR(50)", "@.phonetype
VARCHAR(50)", etc. And you don't seem to be aware of floating
point rounding errors (does your machine have a floating point
processor, or do you want to slow things down with a software floating
point package?)
You keep height in inches or cm then convert it for display. You do
not do this in the database. The syntax for CAST is CAST (<exp> AS
<datatype> ) -- do not use the proprietary CONVERT().
You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
But even worse, you did absolutely no design or research on the data.
Other products have a MERGE or UPSERT statement to do this. The usual
pattern in older products is:
BEGIN
-- insert the new rows
INSERT INTO Foobar
SELECT *
FROM WorkingData AS W
WHERE W.keycol
NOT IN (SELECT keycol FROM Foobar);
-- update the rows are already there
UPDATE Foobar
SET <column>
= (SELECT col FROM WorkingData AS W
WHERE W.keycol = Foobar.keycol)
WHERE keycol IN (SELECT keycol FROM WorkingData);
END;|||Since you have no idea about the situation or the database schema your
posting is just wasted useless space. Please do not bother my thread again
unless you want to know more about the database and can actually just give m
e
a reason to my error. I did not ask for a comments on the coding though
constructive criticism, not code bashing, is appreciated - again I stated I
am a Computer Programmer using SQL code in ASP pages mostly not much done
with SQL Server, I would have done this in VBScript (which I have already
done before) but store procedures are more efficient and reliable.
Oh and the comment about doing design and research on the data, you have no
idea how much design and research in this database I have done. With a
database that all the stored procedures are encrypted (which you cannot
decrypt unless you want to break the software agreement which I play by the
rules maybe you do not), no manuals or references on the database (because i
t
is part of a software program and while they want you to add functionalilty
to the product for your own uses they are not forth coming with how to do
things) and myself being no where near a DBA I think I have a pretty good
understanding of this database which you clearly do not because you did not
ask!
Thanks but no Thanks for your post...this is why I originally did not post
the code because people like you want to just code bash instead of helping!
"--CELKO--" wrote:

> Why did you destroy Standard SQL behavior? Why are there more NULLs in
> one table than should be in an entire Fortune 500 accounting package?
> Do you really have a lot of data elements that are in Chinese and 255
> characters long? Why do you have data type prefixes on variable names,
> which is a violation of both good programming and ISO-11179? Why do you
> have numeric data elements in strings? What kind of total garbage are
> trying to get with things like "weight VARCHAR(50)", "@.phonetype
> VARCHAR(50)", etc. And you don't seem to be aware of floating
> point rounding errors (does your machine have a floating point
> processor, or do you want to slow things down with a software floating
> point package?)
> You keep height in inches or cm then convert it for display. You do
> not do this in the database. The syntax for CAST is CAST (<exp> AS
> <datatype> ) -- do not use the proprietary CONVERT().
> You are NOT writing SQL at all, but some kind of 3GL, using SQL for it.
> But even worse, you did absolutely no design or research on the data.
> Other products have a MERGE or UPSERT statement to do this. The usual
> pattern in older products is:
> BEGIN
> -- insert the new rows
> INSERT INTO Foobar
> SELECT *
> FROM WorkingData AS W
> WHERE W.keycol
> NOT IN (SELECT keycol FROM Foobar);
> -- update the rows are already there
> UPDATE Foobar
> SET <column>
> = (SELECT col FROM WorkingData AS W
> WHERE W.keycol = Foobar.keycol)
> WHERE keycol IN (SELECT keycol FROM WorkingData);
> END;
>