query gives out different results

Hi,
I did post a similar question yesterday - but it seems to have disappeared
from view, so appologies if you have seen this before.
I originally had a program which I sas told to put into a stored procedure.
I have done this BUT, to check that it's working I'm running them against
each other BUT and getting different results, in the final output. I do know
that the query is producing the correct result. I think the only difference
is I have a GO statement in the original program, between where the pquery i
s
run and the query updates the main table.
There are a number of steps to this process.
1. I copy the DISTINCT ref to a new output table (This works OK, and creates
538332 records)
2. I add another column to the output table called TENURE (This also works O
K)
3. I exec the query which has two parts to it. The first part runs a query
which puts the DISTINCT ref into a temporary table (This works ok and create
s
64379 records - as does the original code)
This but looks like:
CREATE TABLE #URN_STEP05
(REF varchar(255))
INSERT URN_STEP05(ref)
SELECT DISTINCT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
AND RESPTYPE='NF Cash Donation'
AND ref IN(SELECT ref
FROM U_NFADHOC
WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
-24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
-12,CONVERT(DATETIME,@.enddate))
AND RESPTYPE='NF Cash Donation')
ORDER BY ref
5. The next bit is copying 70519 records into the output file. I can't work
out why.
UPDATE U_tenure
SET TENURE = 'CORE'
FROM U_tenure
INNER JOIN URN_STEP05
ON U_tenure.REF = URN_STEP05.REF
WHERE U_tenure.REF = URN_STEP05.REF
Is there something I'm doing wrong here? Is it possible to UPDATE the
original table instead of using a temporary table first?
Any help would be appreciated
RobWhy you are creating a temporary table #URN_STEP05
and referencing a permanent table URN_STEP05
in your insert and update statements?
I guess that might be the problem
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
> Hi,
> I did post a similar question yesterday - but it seems to have disappeared
> from view, so appologies if you have seen this before.
> I originally had a program which I sas told to put into a stored
> procedure.
> I have done this BUT, to check that it's working I'm running them against
> each other BUT and getting different results, in the final output. I do
> know
> that the query is producing the correct result. I think the only
> difference
> is I have a GO statement in the original program, between where the pquery
> is
> run and the query updates the main table.
> There are a number of steps to this process.
> 1. I copy the DISTINCT ref to a new output table (This works OK, and
> creates
> 538332 records)
> 2. I add another column to the output table called TENURE (This also works
> OK)
> 3. I exec the query which has two parts to it. The first part runs a query
> which puts the DISTINCT ref into a temporary table (This works ok and
> creates
> 64379 records - as does the original code)
> This but looks like:
> CREATE TABLE #URN_STEP05
> (REF varchar(255))
> INSERT URN_STEP05(ref)
> SELECT DISTINCT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))) AND CONVERT(DATETIME, @.enddate)
> AND RESPTYPE='NF Cash Donation'
> AND ref IN(SELECT ref
> FROM U_NFADHOC
> WHERE CONVERT(DATETIME,DATE) BETWEEN DATEADD(DAY,+1,DATEADD(MONTH,
> -24,CONVERT(DATETIME,@.enddate))) AND DATEADD(MONTH,
> -12,CONVERT(DATETIME,@.enddate))
> AND RESPTYPE='NF Cash Donation')
> ORDER BY ref
> 5. The next bit is copying 70519 records into the output file. I can't
> work
> out why.
> UPDATE U_tenure
> SET TENURE = 'CORE'
> FROM U_tenure
> INNER JOIN URN_STEP05
> ON U_tenure.REF = URN_STEP05.REF
> WHERE U_tenure.REF = URN_STEP05.REF
> --
> Is there something I'm doing wrong here? Is it possible to UPDATE the
> original table instead of using a temporary table first?
> Any help would be appreciated
> Rob
>
>|||Hi Roji,
I am not sure how to put the output direct into the table - when I tried
that it copied CORE to all of the TENURE fields instead of just the 64379
records. Is there a way to put them in directly? If so that would be a lot
better. Can you possably show me how to do this please?
Regards
Rob
"Roji. P. Thomas" wrote:

> Why you are creating a temporary table #URN_STEP05
> and referencing a permanent table URN_STEP05
> in your insert and update statements?
> I guess that might be the problem
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E9433D93-A679-441A-B6E2-F6F566D8BC54@.microsoft.com...
>
>|||Did you read both replies in yesterday's thread?
http://www.google.co.uk/groups?hl=e...40microsoft.com

> 5. The next bit is copying 70519 records into the output file. I
can't work
out why.
As previously stated, UPDATE doesn't "copy records". Your UPDATE will
set the value of Tenure on every row whose Ref is in URN_STEP05. If
that's not what you want then please post enough code actually to
reproduce the problem: DDL, sample data INSERTs and show your required
end result from that sample (not all 70,000 rows - just a few rows but
enough to illustrate the problem.)
David Portas
SQL Server MVP
--