Insert Statement

I have an insert statement which insert recrods from one table into another.
My problem is I am trying to introduce a where clause which doesn't insert
records which have the same values in the FromURN and TOURN columns. In the
example below I don't want record 4 to be inserted because it is the same as
record 3. Can anyone help me achieve this by adding another clause into my
insert statement.
TABLE: MyTable
RecNo FromURN ToURN
1 100 200
2 300 400
3 500 600
4 500 600
5 700 800
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MergeDateMerged
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURNINSERT 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
GROUP BY MergeFromURN, MergeToURN
You didn't specify which MergeDate you want to use if you have to identical
MergeFromURN and MergeToURN, but the lowest seems to make the most sense.
Jacco Schalkwijk
SQL Server MVP
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:AF65A31B-8119-4319-BB92-6B6386CB16A1@.microsoft.com...
>I have an insert statement which insert recrods from one table into
>another.
> My problem is I am trying to introduce a where clause which doesn't insert
> records which have the same values in the FromURN and TOURN columns. In
> the
> example below I don't want record 4 to be inserted because it is the same
> as
> record 3. Can anyone help me achieve this by adding another clause into
> my
> insert statement.
> TABLE: MyTable
> RecNo FromURN ToURN
> 1 100 200
> 2 300 400
> 3 500 600
> 4 500 600
> 5 700 800
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MergeDateMerged
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN|||Or
INSERT INTO ....
SELECT * FROM myTable
WHERE RecNo NOT IN
(
SELECT B.RecNo
FROM myTableA JOIN myTable B
ON A.RecNo < B.RecNo
AND A.FromURN = B.FromURN
)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:AF65A31B-8119-4319-BB92-6B6386CB16A1@.microsoft.com...
>I have an insert statement which insert recrods from one table into
>another.
> My problem is I am trying to introduce a where clause which doesn't insert
> records which have the same values in the FromURN and TOURN columns. In
> the
> example below I don't want record 4 to be inserted because it is the same
> as
> record 3. Can anyone help me achieve this by adding another clause into
> my
> insert statement.
> TABLE: MyTable
> RecNo FromURN ToURN
> 1 100 200
> 2 300 400
> 3 500 600
> 4 500 600
> 5 700 800
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MergeDateMerged
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN|||Stephen, Since you don't want to insert records with the same FromUrn and To
Urn
values, this should also help.
INSERT INTO MOVE(MoveFromURN, MoveToURN, MoveDateMerged)
select t.FromUrn, t.ToUrn, t.DateTimeStamp
from myTable t
where t.RecordNumber NOT IN
(select t1.RecordNumber
from myTable t1, myTable t2
where t1.FromUrn = t2.FromUrn
and t1.ToUrn = t2.ToUrn
and t1.RecordNumber != t2.RecordNumber)
UNION
select top 1 t1.FromUrn, t1.ToUrn, t1.DateTimeStamp
from myTable t1, myTable t2
where t1.FromUrn = t2.FromUrn
and t1.ToUrn = t2.ToUrn
and t1.RecordNumber != t2.RecordNumber
I am interested in why there is not an nonclustered index on the columns
FromUrn
and ToUrn in your Table MyTable. Thank you.
"Stephen" wrote:

> I have an insert statement which insert recrods from one table into anothe
r.
> My problem is I am trying to introduce a where clause which doesn't insert
> records which have the same values in the FromURN and TOURN columns. In t
he
> example below I don't want record 4 to be inserted because it is the same
as
> record 3. Can anyone help me achieve this by adding another clause into m
y
> insert statement.
> TABLE: MyTable
> RecNo FromURN ToURN
> 1 100 200
> 2 300 400
> 3 500 600
> 4 500 600
> 5 700 800
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MergeDateMerged
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN|||Stephen, I apologize for the earlier post. It should read:
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
select t.FromUrn, t.ToUrn, t.DateTimeStamp
from myTable t
where t.RecordNumber NOT IN
(select t1.RecordNumber
from myTable t1, myTable t2
where t1.FromUrn = t2.FromUrn
and t1.ToUrn = t2.ToUrn
and t1.RecordNumber != t2.RecordNumber)
UNION
select t1.FromUrn, t1.ToUrn, t1.DateTimeStamp
from myTable t1
where t1.RecordNumber IN
(SELECT MIN(ta.RecordNumber)
from myTable ta, myTable tb
where ta.FromUrn = tb.FromUrn
and ta.ToUrn = tb.ToUrn
and ta.RecordNumber != tb.RecordNumber
group by ta.FromUrn, ta.ToUrn)
"Stephen" wrote:

> I have an insert statement which insert recrods from one table into anothe
r.
> My problem is I am trying to introduce a where clause which doesn't insert
> records which have the same values in the FromURN and TOURN columns. In t
he
> example below I don't want record 4 to be inserted because it is the same
as
> record 3. Can anyone help me achieve this by adding another clause into m
y
> insert statement.
> TABLE: MyTable
> RecNo FromURN ToURN
> 1 100 200
> 2 300 400
> 3 500 600
> 4 500 600
> 5 700 800
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MergeDateMerged
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN