help with while loops - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

help with while loops

Author

Topic

smithani
Starting Member

42 Posts

Posted - 2007-08-23 : 16:30:59

hi,
I am trying to
1)get all the names of a table that match another table
2)if count=0, then I want to insert into another table 'group' after getting its key.

I am totally lost, can somebody point me in the right direction.

how do I get the individual name so that I can insert into the group table,
I tried 'select @name=name, that gave no results'.
Thanks

while (SELECT name FROM names WHERE name in (select name from Group) and status = 'M' )=0
begin
insert into group(group_id,name,action) values (@key, name, 'play' )
end

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 16:42:08

[code]
SELECT * FROM SysObjects WHERE Type ='U' AND Name like '%' + @Somename + '%'
[/code]
will give you names similar to @somename. If your @somename needs to be an exact match you can try
[code]
SELECT * FROM SysObjects WHERE Type ='U' AND Name = @Somename
[/code]
And if its coming from a list
[code]
SELECT * FROM SysObjects WHERE Type ='U' AND Name IN (SELECT Names from Group)
[/code]

You can use IF EXISTS or IF NOT EXISTS to check for existence of records in a table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-08-23 : 16:44:24

What is key? How you going to generate it?

If not exists(select * from names n join group g on n.name = g.name)
Begin
   Insert into Group
   Select @key, name, 'play'
   from names n left join group g
   on n.name = g.name
   where g.name is null
End

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

smithani
Starting Member

42 Posts

Posted - 2007-08-23 : 16:53:36

Want I wnat to do, is check to see if names dont exist on a table,
if they do, I wnat to skip that name from inserting , but if it does I want to get that name and insert into another table, after getting the key, I have a separate function to do that, which is working great..
in this example

If not exists(select * from names n join group g on n.name = g.name)
Begin
Insert into Group
Select @key, name, 'play' ------ HOW DOES THE INDIVIDUAL NAME VALUE GET HERE, can you plesae explain
from names n left join group g
on n.name = g.name
where g.name is null
End

smithani
Starting Member

42 Posts

Posted - 2007-08-23 : 16:53:40

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 17:03:03

[code]INSERT [Group]
(
Group_Id,
[Name],
[Action]
)
SELECT @Key,
n.Name,
'Play'
FROM Names AS n
LEFT JOIN [Group] AS g ON g.Name = n.Name
WHERE n.Status = 'M'
AND g.Name IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources