insert statement

Hello,
I'm trying to insert into another table where select clause contains a join
with another table. However the number of columns of the source does not
match with the destination table.
How can i fix this without specifying all columns?Why wouldn't you want to specify the required columns? I strongly
recommend you always list the column names. In the long run it makes
your code much more stable and maintainable.
Your options are basically to list only the required columns or to
create a view containing only the required columns and select from that
instead. Of course you'll then have to list the columns in the view...
Either way, you can save yourself a lot of typing by clicking and
dragging the column name list from the Object Browser in query
analyzer.
David Portas
SQL Server MVP
--|||Hi David,
Of course i don't want to type a lot. But the only difference in the
sourcetable is just one field and that field comes from another database.
Will it work?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109605400.131290.70710@.f14g2000cwb.googlegroups.com...
> Why wouldn't you want to specify the required columns? I strongly
> recommend you always list the column names. In the long run it makes
> your code much more stable and maintainable.
> Your options are basically to list only the required columns or to
> create a view containing only the required columns and select from that
> instead. Of course you'll then have to list the columns in the view...
> Either way, you can save yourself a lot of typing by clicking and
> dragging the column name list from the Object Browser in query
> analyzer.
> --
> David Portas
> SQL Server MVP
> --
>|||Will it work? Maybe it will. But I don't believe it is guaranteed to work
unless you specifically list the columns in both lists. Is it worth the
risk? That's up to you but I certainly would not want to put into
production code that I was not sure was correct.
Andrew J. Kelly SQL MVP
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ucxlZ$aHFHA.3588@.TK2MSFTNGP14.phx.gbl...
> Hi David,
> Of course i don't want to type a lot. But the only difference in the
> sourcetable is just one field and that field comes from another database.
> Will it work?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109605400.131290.70710@.f14g2000cwb.googlegroups.com...
>|||Will what work? If there are fewer columns in the SELECT statement than
in the target table then you have to list the columns in the INSERT
list and/or the SELECT list - SQL Server won't guess which columns you
want to keep.
David Portas
SQL Server MVP
--|||You can copy and paste the column names out of EM (Design Table) and into
Excel, then use Excel to concatenate them into one long comma-separated
string for you. You can then copy and paste that one long string into your
editing program to eliminate some typing.
Thx
Mike C
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ucxlZ$aHFHA.3588@.TK2MSFTNGP14.phx.gbl...
> Hi David,
> Of course i don't want to type a lot. But the only difference in the
> sourcetable is just one field and that field comes from another database.
> Will it work?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109605400.131290.70710@.f14g2000cwb.googlegroups.com...
>

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