INSERT & SELECT using Right Outer Join

Question

Post reply

INSERT & SELECT using Right Outer Join

sharma-691993

SSCrazy

Points: 2194
More actions
June 8, 2008 at 3:29 am

#188728

Hi i want to insert TEST1 table ID from Select query.
INSERT INTO TEST1[ID,NAME,EMAIL]
SELECT A.ID,'Alex','alex@gmail.com',B.ID
FROM TEST2
RIGHT OUTER JOIN TEST2 B ON A.ID=B.ID WHERE A.ID IS NULL
While executing the above query it shows
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

haroon waheed SSC Veteran Points: 261 More actions · Answer 1

The insert into table has only three columns specified.

Your select however has four columns selected

sharma-691993 SSCrazy Points: 2194 More actions · Answer 2

Thanks for ur reply..

I had changed the query as follows

INSERT INTO TEST1[ID,NAME,EMAIL]

SELECT A.ID,'Alex','alex@gmail.com'

FROM TEST2

RIGHT OUTER JOIN TEST2 B ON A.ID=B.ID WHERE A.ID IS NULL

now also it shows bellow error..

While executing the above query it shows

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Raghuram (AJ) Ten Centuries Points: 1214 More actions · Answer 3

sharma (6/8/2008)
Thanks for ur reply..
I had changed the query as follows
INSERT INTO TEST1[ID,NAME,EMAIL]
SELECT A.ID,'Alex','alex@gmail.com'
FROM TEST2
RIGHT OUTER JOIN TEST2 B ON A.ID=B.ID WHERE A.ID IS NULL
now also it shows bellow error..
While executing the above query it shows
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

The following may be of some help:

1. If you are using [] brackets ([ID, NAME, EMAIL]), you may want to replace them by normal paranthesis ().

2. The alias "A" is not associated with any table in your example.

3. Is ID an identitiy column on TEST1? If yes, is IDENTITY_INSERT set to ON or OFF?

The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

sharma-691993 SSCrazy Points: 2194 More actions · Answer 4

There is no IDENTITY column in below table..

Just i am doing select one value using SELECT and hardcode two values during insertion.. The above scenario works in normal INSERT & SELECT, but i didn't get in JOINS..

INSERT INTO TEST1(ID,NAME,EMAIL)

SELECT A.ID,'Alex','alex@gmail.com'

FROM TEST2 A

RIGHT OUTER JOIN TEST2 B ON A.ID=B.ID WHERE A.ID IS NULL

Thanks

Vladan SSC-Insane Points: 22072 More actions · Answer 5

And what is your problem now? Does it still throw an error or just nothing gets inserted, or not the correct number of rows is inserted, or ...?

As far as I can see, the query should have correct syntax now, but it still is a bit strange. You're doing right join, and have WHERE A.ID IS NULL condition on table A, while you are inserting A.ID into your result. A.ID will therefore always be inserted as NULL, and one row should be inserted for each row in table B that does not have any corresponding row in table A. I suspect that this is not what you wanted. Maybe you should change it to LEFT JOIN and WHERE B.ID IS NULL, or insert B.ID instead of A.ID?

Just try to describe in words what you want to do - it is nearly impossible to find out from a non-working query what it was supposed to do.

EDIT : Just now I realized that A and B are just two instances of the same table. You can hardly suppose that a row will be missing in any of them - they are identical, so your query can't return any rows. So here we are back again at the question "what do you want to do?"