problem when inserting data. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

problem when inserting data.

Author

Topic

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2008-08-01 : 06:54:01

Hi,

here is the task i have. i have two tables.
table 1 TBL_DontAllowProductToEnroll have following data..

DAlow_ID Orp_DAlow_ProductId DAlow_Orp_Id pln_DAlow_PlanTypeId Dallow_Type
1 403 404 403 D
2 798 793 798 D
3 798 792 798 D
4 798 788 798 D
5 838 796 838 D
6 838 790 838 D

the above datas should be inserted into another table called TBL_MustHaveConfigData like this..

MD_ID Orp_MD_SourceProductId Orp_MD_DestProductID MD_MathOperator MD_LogOperator

1 403 26 = NULL
2 798 793 = OR
3 798 792 = OR
4 798 788 = NULL
5 838 796 = OR
6 838 790 = NULL

if Orp_DAlow_ProductId of TBL_DontAllowProductToEnroll have only one row of data then MD_LogOperator of TBL_MustHaveConfigData should be null. if Orp_DAlow_ProductId has multiple rows of data then when inserting into TBL_MustHaveConfigData , the last record should have MD_LogOperator as null for that particular product.

How to achive this..?

Thanks in advance.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-01 : 08:17:14

Where does "26" come from?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-01 : 08:25:20

Something like this?

--sample data
declare @TBL_DontAllowProductToEnroll table (
DAlow_ID int, Orp_DAlow_ProductId int, DAlow_Orp_Id int, pln_DAlow_PlanTypeId int, Dallow_Type char(1))
insert @TBL_DontAllowProductToEnroll
          select 1, 403, 404, 403, 'D'
union all select 2, 798, 793, 798, 'D'
union all select 3, 798, 792, 798, 'D'
union all select 4, 798, 788, 798, 'D'
union all select 5, 838, 796, 838, 'D'
union all select 6, 838, 790, 838, 'D'

--query
; with
  a as (select *, row_number() over (partition by Orp_DAlow_ProductId
        order by DAlow_ID desc) as row from @TBL_DontAllowProductToEnroll)
select DAlow_ID as MD_ID, Orp_DAlow_ProductId as Orp_MD_SourceProductId,
    DAlow_Orp_Id as Orp_MD_DestProductID, '=' as MD_MathOperator,
    case row when 1 then null else 'OR' end as MD_LogOperator
from a order by MD_ID

/* results
MD_ID       Orp_MD_SourceProductId Orp_MD_DestProductID MD_MathOperator MD_LogOperator
----------- ---------------------- -------------------- --------------- --------------
1           403                    404                  =               NULL
2           798                    793                  =               OR
3           798                    792                  =               OR
4           798                    788                  =               NULL
5           838                    796                  =               OR
6           838                    790                  =               NULL
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2008-08-01 : 08:33:50

That worked..
thanks RyanRandall for quick reply.

also i found another way..

insert into @TBL_MustHaveConfigData
select
orp_dalow_productid,
,DAlow_Orp_Id ,
'=',
Case
When
(Select max(DAlow_ID) from TBL_DontAllowProductToEnroll have a where a.Orp_DAlow_ProductId=b.Orp_DAlow_ProductId
group by a.Orp_DAlow_ProductId) <>DAlow_ID Then
'OR'
End
from TBL_DontAllowProductToEnroll b
order by Orp_DAlow_ProductId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources