Insert Record Logic Needed

Im trying to write some sql which inserts these rows into another table
called move. Unfortunately i am struggling trying to get the insert statemen
t
not to insert record 5 because the ToURN has been used before in a previous
record(1). Does anyone know how I would write the sql to do this.
RecNO MergeFromURN MergeToURN MergeDateMerged
1 100 200 15/06/1982
2 200 300 15/06/1982
3 300 400 15/06/1982
4 500 600 15/06/1982
5 700 100 15/06/1982
6 100 100 15/06/1982
7 NULL 100 15/06/1982
8 700 0 15/06/1982
So far I have the following sql but need to go that step further to stop
record 5 being inserted because 100 already has been inserted as a from urn
in record 1.
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
(select MoveToURN from Move))
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN
while @.@.ROWCOUNT > 0
begin
update A set MoveToURN = B.MoveToURN
from Move A
inner join Move B on A.MoveToURN=B.MoveFromURN
end
Can anyone help me with this.Are you aware of the fact that at least once several people hav tried to hel
p
you?
Maybe you should read their responses...
http://msdn.microsoft.com/newsgroup...92-6b6386cb16a1
ML

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceUse Profiler to trace the statements sent to sql server. Start by using the
duration template and filter for duration greater than, let us say, 1000
milliseconds or whatever amount you decide. See BOL for more information
about "Profiler".
AMB
"RG" wrote:

> I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
> application written accessing this SQL Server 7 db. Unfortunately, throug
h a
> series of workstation upgrades, the source code was lost.
> Recently, the performance of table inserts degraded significantly. I am
> trying figure out as to what is causing it.
> I am guessing there is some sort of locking problem going on.
> Is there a utility which allows you to trace ODBC activity?
> If anyone knows of any other utility or method to diagnose the problem, I
> would greatly appreciate help.
> Thanks in advance|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
How can I find the query behind this cursor?
Thanks
"Alejandro Mesa" wrote:
> Use Profiler to trace the statements sent to sql server. Start by using th
e
> duration template and filter for duration greater than, let us say, 1000
> milliseconds or whatever amount you decide. See BOL for more information
> about "Profiler".
>
> AMB
> "RG" wrote:
>|||See if this helps:
Server Side Cursors and ADO Cursor Types
http://www.sqlteam.com/item.asp?ItemID=11842
AMB
"RG" wrote:
> Thanks for your help.
> It appears the following statement is a problem
> sp_cursorfetch 422977628, 2, 0, 1.
> How can I find the query behind this cursor?
> Thanks
> "Alejandro Mesa" wrote:
>|||It looks like this is it. It also appears that if you don't have the source
,
there is not much you could do on the database side to improve this
performance.
"Alejandro Mesa" wrote:
> See if this helps:
> Server Side Cursors and ADO Cursor Types
> http://www.sqlteam.com/item.asp?ItemID=11842
>
> AMB
> "RG" wrote:
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
If you think there is some locking, blocking going on then
you likely want to monitor this in SQL Server. You can start
by using the system stored procedures sp_lock, sp_who2 and
querying master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Thu, 17 Feb 2005 08:39:09 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
>application written accessing this SQL Server 7 db. Unfortunately, through a
>series of workstation upgrades, the source code was lost.
>Recently, the performance of table inserts degraded significantly. I am
>trying figure out as to what is causing it.
>I am guessing there is some sort of locking problem going on.
>Is there a utility which allows you to trace ODBC activity?
>If anyone knows of any other utility or method to diagnose the problem, I
>would greatly appreciate help.
>Thanks in advance

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
my 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND i
f
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:
> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceIf you think there is some locking, blocking going on then
you likely want to monitor this in SQL Server. You can start
by using the system stored procedures sp_lock, sp_who2 and
querying master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Thu, 17 Feb 2005 08:39:09 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
>application written accessing this SQL Server 7 db. Unfortunately, through
a
>series of workstation upgrades, the source code was lost.
>Recently, the performance of table inserts degraded significantly. I am
>trying figure out as to what is causing it.
>I am guessing there is some sort of locking problem going on.
>Is there a utility which allows you to trace ODBC activity?
>If anyone knows of any other utility or method to diagnose the problem, I
>would greatly appreciate help.
>Thanks in advance