Looping through each record in a Table

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 2008 Forums

Transact-SQL (2008)

Looping through each record in a Table

Author

Topic

dineshreddy
Starting Member

7 Posts

Posted - 2011-03-30 : 22:37:45

(X)- Don't bother

Table1 - T1Col1(varchar) T1Col2(int) T1Col3(varchar)(X)

limno 0 a
jim 1 b
ruth 2 c
tim 3 d
kim 1 e

Table2- T2Col1(varchar)(X), T2Col2(varchar), T2Col3(int)

a admin 1
b user 2
c general 4
d security 8

Result Table :
Table3- T3col1(varchar),T3col2(varchar)
jim admin
ruth user
tim admin
tim user

For every record in Table1 I need to loop through top 10 records in Table2 order by T2col3 - check this condition if( T1Col2 element & T2Col3 element)/T2Col3 element = 1 and insert T1Col1,T2col1 in to Table3
& is bit-wise and operation

Example for ruth
cast((2 & 1) as int )/1) ->0 donot insert ruth,admin result is 0
cast((2 & 2) as int )/2) ->1 insert ruth,user result is 1
cast((2& 4) as int )/4) ->0 donot insert ruth,general result is 0
cast((2 & 8) as int)/8) ->0 donot insert ruth,security result is 0

like this I need to loop for each record in table 1 and check with top 10 records in table2 making insertions in table3

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 08:11:10

I read your description, but I got confused by couple of things.

1. When you say "top 10" records in Table 2, how do you define those? To define top 10, you need some sort of ordering scheme. Is it ordered by T2Col3, for example?

2. I am assuming that the "&" is bit-wise and. If so I didn't quite follow how ruth has two rows in your results - admin and user - because ruth has 2 in Table1 and admin is 1 in Table 2.

dineshreddy
Starting Member

7 Posts

Posted - 2011-03-31 : 08:42:16

quote:
Originally posted by sunitabeck

I read your description, but I got confused by couple of things.

1. When you say "top 10" records in Table 2, how do you define those? To define top 10, you need some sort of ordering scheme. Is it ordered by T2Col3, for example?

2. I am assuming that the "&" is bit-wise and. If so I didn't quite follow how ruth has two rows in your results - admin and user - because ruth has 2 in Table1 and admin is 1 in Table 2.

Order by T2Col3 and I make up the result set just need to check the condition and make insertions. It is bitwise and. I have updated the changes in the first post.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 10:55:57

[code]-- CREATING TEST DATA
create table #t1 (T1Col1 varchar(32), T1Col2 int);
create table #t2 (T2Col2 varchar(32), T2Col3 int );

insert into #t1 values ('limno','0');
insert into #t1 values ('jim','1');
insert into #t1 values ('ruth','2');
insert into #t1 values ('tim','3');
insert into #t1 values ('kim','1');

insert into #t2 values ('admin','1');
insert into #t2 values ('user','2');
insert into #t2 values ('general','4');
insert into #t2 values ('security','8');

select * from #t1;
select * from #t2;

-- THIS QUERY IMPLEMENTS THE RULE YOU DESCRIBED.
;with CTE as
( select top 10 * from #t2 order by T2Col3 )
--insert into YourNewTable
select
T1Col1,
T2Col2
from
#t1 t1
inner join CTE on 1 = (T2Col3 & T1Col2)/T2Col3

-- CLEANS UP TEST DATA
drop table #t1;
drop table #t2;
[/code]

dineshreddy
Starting Member

7 Posts

Posted - 2011-03-31 : 14:51:24

ThankYou

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources