Insert Results From 2 SELECT statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert Results From 2 SELECT statements

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2002-08-07 : 11:33:19

I'm using VB6 against an sql server database.
My tables aren't designed properly but I'm trying to do an insert based on the results from two different select statements.
Can someone help me with the sql here?:
(Or maybe it can't be done.)
------------------------------------------------------
I hard-coded the values for now but can you let me know how I would do the insert when I have to insert the results from two different select statements.
Here's what I'm trying to insert:
----------------------------------------------------
insert into delegationmembership (memberid,delegationid,lastactionuser)
select requesters.id from requesters
where typecode = 'M' and name = 'BRAGMAN'
select requesters.id from requesters
where typecode = 'D' and name = 'BRONX',
'Johnson'
--------------------------------------------------

thanks

John

macka
Posting Yak Master

162 Posts

Posted - 2002-08-07 : 11:34:41

Assuming that delegationid,lastactionuser ARE nullable columns:

select requesters.id from requesters
where typecode = 'M' and name = 'BRAGMAN'
UNION
select requesters.id from requesters
where typecode = 'D' and name = 'BRONX',

macka.

Edited by - macka on 08/07/2002 11:36:12

jtrapat1
Starting Member

43 Posts

Posted - 2002-08-07 : 11:57:10

macka,
can you explain that a little better?
i still don't see how to insert the individual values.
Do I have to use VALUES and then the select statements?

Thanks
John

macka
Posting Yak Master

162 Posts

Posted - 2002-08-07 : 12:01:51

Sorry, I slightly mis-read the question. You could do something like this:

insert into delegationmembership (memberid,delegationid,lastactionuser)
select requesters.id from requesters
where typecode = 'M' and name IN('BRAGMAN','...etc...')
UNION
select requesters.id from requesters
where typecode = 'D' and name IN ('BRONX','Johnson','..etc...')

The INSERT statement accepts either scaler values or a valid SQL SELECT. In this case you are able to UNION the results of the SELECT queries into a single result set.

HTH,

macka.

jtrapat1
Starting Member

43 Posts

Posted - 2002-08-07 : 12:09:39

macka,
My problem is that the results from these two select statements will be different numbers.
For example, they will return two separate ID numbers (63, 3) that I want to insert into the database table as two distinct items.
So i don't want to union them together.

Is there a way to do this?

Sorry if I'm not explaining it very well.
It's the way my tables are designed.
Both of the values in the id column are linked to different types.

thanks
john

macka
Posting Yak Master

162 Posts

Posted - 2002-08-07 : 12:12:54

quote:

macka,
My problem is that the results from these two select statements will be different numbers.
For example, they will return two separate ID numbers (63, 3) that I want to insert into the database table as two distinct items.
So i don't want to union them together.

Using the previous INSERT syntax I provided, it will insert them as 2 distinct items.

macka.

jtrapat1
Starting Member

43 Posts

Posted - 2002-08-07 : 12:19:41

Here's the error message I get after running this query:

--------------------------------------------------
insert into delegationmembership (memberid,delegationid)
select requesters.id from requesters
where typecode = 'M' and name = 'BRAGMAN'
UNION
select requesters.id from requesters
where typecode = 'D' and name = 'BRONX'

--------------------------------------------------
Server: Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
----------------------------------------------------
When I run the select query by itself, I get the correct values that I want to insert:

----------------------------------------------------
id
--------------------
63
3

(2 row(s) affected)

macka
Posting Yak Master

162 Posts

Posted - 2002-08-07 : 12:23:56

Thats because you have specified in the INPUT that there are 2 columns you wish to insert, however you are only passing in one value. You need to supply both values:

insert into delegationmembership (memberid,delegationid)
select requesters.id,delegationid from requesters
where typecode = 'M' and name = 'BRAGMAN'
UNION
select requesters.id,delegationid from requesters
where typecode = 'D' and name = 'BRONX'

or if delegationid is the same for all, you could do something like this:

insert into delegationmembership (memberid,delegationid)
select requesters.id,123 from requesters
where typecode = 'M' and name = 'BRAGMAN'
UNION
select requesters.id,123 from requesters
where typecode = 'D' and name = 'BRONX'

macka.

jtrapat1
Starting Member

43 Posts

Posted - 2002-08-07 : 13:04:17

The results of my two select statements are each two separate values.
I probably can't do it this way but I want to select two distinct values and insert them into ONE row.
The only thing that's different is the WHERE clause.

For example, if:
select requesters.id from requesters
where typecode = 'M' and name = 'BRAGMAN'
is 63
and
select requesters.id from requesters
where typecode = 'D' and name = 'BRONX'
is 3
I want to insert this:
insert into delegationmembership (memberid,delegationid)
values (63,3)

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-08-07 : 13:49:05

insert into delegationmembership (memberid,delegationid)
select (select requesters.id from requesters
where typecode = 'M' and name = 'BRAGMAN' )
,
(select requesters.id from requesters
where typecode = 'D' and name = 'BRONX' )

jtrapat1
Starting Member

43 Posts

Posted - 2002-08-07 : 14:17:42

thanks,

I finally got the statement working.
Here's what I wrote:
insert into delegationmembership (memberid,delegationid,lastactionuser)
(select R.id,
(select S.id from requesters S where typecode = 'D' and name = 'BRONX'),
'johnson'
from requesters R
where typecode = 'M' and name = 'BRAGMAN')

thanks again
john

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources