Insert values based on conditions.

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

Transact-SQL (2005)

Insert values based on conditions.

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-22 : 21:16:31

Hello,
I have two tables.
P and Q.
P

ID       NUM
1        23
1        12
1        34
2         9
3         5
2         6
1         4
....

ID     MIN         MAX
1        5          10
2        0          1
3        2          2

I want to insert values into P if the count rows of a specfic ID within the range in table Q.

select count(*) from P
where ID=1

if MIN<=count(*)<=MAX

I guess the code could look like:

insert into p
values(1,15)
where p.id=1 and rowcnt between q.low and q.high

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 00:55:29

what will be values you will be inserting for NUM ield? just a random value?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-23 : 08:38:48

NUM is a given value.
When the condition is satisfied just insert it.Otherwise if count(*) beyonds the range, it is to
prevent for insertion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 10:17:32

May be this:-

INSERT INTO P(ID,NUM)
SELECT q.ID,@Num
FROM Q q
INNER JOIN (SELECT ID,COUNT(1) AS RecCnt FROM P GROUP BY ID) p
ON p.ID=q.ID
AND p.RecCnt BETWEEN q.MIN AND q.MAX

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources