Multiple Inserts Query? 1

[rpangel]

hi everyone,

i was wondering if anyone knew the syntax for creating a query that will do multiple inserts.

for example: in tabel UserService i want to insert the value "None" in the Service field of the table where the GroupIDs in the table equals 1, 2, 3, 4, and 5.

if anyone has any ideas i'd greatly appreciate it.

thanks in advance,
angel

 

[tlbroadbent]

Update UserService Set Service='None'
Where GroupID Between 1 And 5
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rpangel]

thanks, i'll try that but what if it isn't actually 1 through 5, can i have
where GroupID = 1 and 4 and 9
or
where GroupID in(1,4,9)
or
where GroupID = 1, 4, 9

which one of these do you think would work? and just to be clear: an update can work to insert "None" even though there was never a "None" value there??

 

[tlbroadbent]

Where GroupID In (1,4,9) will work. The other clauses are not valid SQL syntax. You can also use

Where GroupID=1 Or GroupID=4 Or GroupID=9

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rpangel]

thanks terry,

do you have any suggestions as to how i can do the same thing, but kind of flipped... for example if in the table UserService I want to insert 13 new Services in the Service field for all the GroupIDs...

something like
update UserService

set Service = "None", "Some", "More", "Few",....(total of 13)...,
or
set Service in("None", "Some",.....,)

where GroupID is not null
or
where GroupID exists

which set and where statements do you think would get the job done?

 

[tlbroadbent]

None! :-(

You need to tell SQl Server how to decide which value to store in the column. For example,

Update UserService Set Service=
Case GroupID
When 1 Then 'None'
When 2 Then 'Some'
When 3 Then 'More'
When 4 Then 'Few'
When 5 Then ...
Else 'Hope this is what you want'
End Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[rpangel]

i wanted all 13 services to be inserted into each groupID (profile). i apologize if i'm confusing you but that case statement didn't look like it was doing that ) do you know if that is the only way possible or do i have other options?

 

[tlbroadbent]

I don't understand. Maybe you could post a sample of how the table should look after running the query. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[vasah20]

It seems like you are having the one column be updated for only specific groups, yes?

then why not try:

update userService set service = "form1,form2,form3..."
where groupId in (1,2,4,...)

hope this helps
leo

 

[rpangel]

will an update statement insert the new 13 services into the table even if the services never existed?

example of output:
Table: UserService
GroupID Service
111 Execute <-- already in table
111 None <---|
111 Some <---|these services are the new ones that i want to
111 Few , etc <---|insert under services, they didn't exist before
112 None <--|
112 Some <--|but i want to be able to insert the new services in
112 Few, etc <--|all of the profiles that exist(over 500)


will
update UserService
set Service = &quot;None&quot;, &quot;Some&quot;, &quot;Few&quot;, etc.
where GroupID is not null;
work even though None, Some and Few was never in the table?

 

[tlbroadbent]

To insert new services you could do the following. [ul]set nocount on

/* Create temp table for services */
Create table #Services (Service varchar(12))

Insert #Services values('Few')
Insert #Services values('Some')
Insert #Services values('None')
Insert #Services values('All')
Insert #Services values('More')
.
. Create an insert for all the service values
.

/* Insert new values into UserService table */
Insert UserService
Select distinct GroupID, b.Service
From UserService a, #Services b
Where GroupID Not In
(Select GroupID From UserService
Where Service=b.Service)

/* Drop temp table */
Drop table #Services

set nocount off[/ul]Let me know how it works. Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin