none
how to insert these records with such conditions

    Question

  • Hi I have one req in which I need to insert some records but with some condition like I have columns like,

    ID    YEAR    ADMISSION    RACE    RECORD  

    now consider first three as INDEX so now column RACE is floting like

    ASIAN, HISPANIC, AMERICAN, BLACK, NON-HISPANIC (fixed to 5 values)

    Now what I want is I need to find first three columns combination and check if any of these five values are there or not if not then insert this value and make a RECORD column as 0 i.e.

    ID    YEAR    ADMISSION        RACE                 RECORD  

    1       2008          y                 AMERICAN                 45

    2       2008          y                 ASIAN                        40

    3       2008          y                 BLACK                       4

    4       2008          y                 HISPANIC                  5

    So now first first three column combination , in RACE column we don't see NON-HISPANIC so i want to insert that and look it like

    ID    YEAR    ADMISSION        RACE                 RECORD  

    1       2008          y                 AMERICAN                 45

    2       2008          y                 ASIAN                        40

    3       2008          y                 BLACK                       4

    4       2008          y                 HISPANIC                  5

    5       2008        y                 non-HISPANIC         0

    I mean it is easy to do it manually but considering the fact that I have total 200+ DISTICT count on first three column combination.

    Please let me know.


    ANK HIT - if reply helps, please mark it as ANSWER or helpful post

    Tuesday, October 08, 2013 8:16 PM

Answers

  • Hi ANK,

    Please try the following codes:

    declare @tempTb table
    (
     ID int,
     [Year] int,
     ADMISSION char(1),
     RACE varchar(20),
     RECORD int
    )
    
    declare @Dic table
    (
     ID int,
     RACE varchar(20)
    )
    
    insert into @Dic (id,RACE) values(1,'AMERICAN'),(2,'ASIAN'),(3,'BLACK'),(4,'HISPANIC'),(5,'non-HISPANIC')
    
    insert into @tempTb (id,[Year],ADMISSION,RACE,RECORD) values
    (1       ,2008          ,'y'                 ,'AMERICAN'                 ,45),
    (2       ,2008          ,'y'                 ,'ASIAN'                 ,40),
    (3       ,2008          ,'y'                 ,'BLACK'                 ,4),
    (4       ,2008          ,'y'                 ,'HISPANIC'                 ,5),
    (1       ,2008          ,'x'                 ,'AMERICAN'                 ,45),
    (2       ,2008          ,'x'                 ,'ASIAN'                 ,40),
    (3       ,2008          ,'x'                 ,'BLACK'                 ,4);
    
    ;with CTE as(
    select distinct [year],ADMISSION 
    ,stuff((
      select ','''+RACE+''''
      from @tempTb t2
      where t1.[Year]=t2.[Year]
      and t1.ADMISSION = t2.ADMISSION
      for xml path('')),1,1,''
     ) as cols
    from @tempTb t1
    group by [year],ADMISSION
    ),cte2 as(
    select cte.[year],cte.ADMISSION,d.ID,d.RACE
    from cte 
    inner join @Dic as d on 1=1)
    insert into @tempTb (id,[Year],ADMISSION,RACE,RECORD) 
    select cte2.ID,cte2.[year],cte2.ADMISSION,cte2.RACE,0 from cte2
    left OUTER JOIN @tempTb t
    ON cte2.[year]=t.[Year]
    AND cte2.ADMISSION=t.ADMISSION
    AND cte2.ID =t.ID
    WHERE t.id IS NULL
    
    SELECT * FROM @tempTb
    order BY [year],ADMISSION;

    Best Regards,
    Allen Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 10, 2013 11:54 AM
    Moderator

All replies

  • declare @race table (race varchar(50));
    
    insert @race 
    values ('ASIAN');
    insert @race 
    values ('HISPANIC');
    insert @race 
    values ('AMERICAN');
    insert @race 
    values ('BLACK');
    insert @race 
    values ('NON-HISPANIC');
    
    
    select r.race
    , 2008 as [year]
    , 'y' as Admission
    , count(*) as Record
    from @race r
    left outer join tbl
      on r.race = tbl.Race 
      and tbl.Year = 2008 
      and tbl.Admission = 'y'
    group by r.race;


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, October 08, 2013 8:51 PM
  • Sorry you lost me, what does this do?

    ANK HIT - if reply helps, please mark it as ANSWER or helpful post

    Tuesday, October 08, 2013 9:02 PM
  • It adds a non-Hispanic row to a set that has no non-Hispanic row.

    I had to majorly guess at what your structures is.

    So apparently I'm lost.  What were you trying to do?  Why don't you just

    insert into tbl (ID,    YEAR ,   ADMISSION ,   RACE ,   RECORD  )

    values (5, 2008, 'y', 'non-Hispanic', 0);


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, October 08, 2013 9:06 PM
  • this is perfect but I also have 158 distinct cmbination of first three columns like

    ID 2,3,4,5,6,7,8, ETC

    YEAR 2000 TO 2013

    ADMISSION YES, NO ,NA

    and it might be HISPANIC missing in some or ASIAN or may be multiple


    ANK HIT - if reply helps, please mark it as ANSWER or helpful post

    Tuesday, October 08, 2013 9:13 PM
  • with id as (
    select distinct id from tbl
    )
    , yr as (
    select distinct [year] from tbl
    )
    , ad as (
    select distinct Admission from tbl
    )
    , 
    , rc as (
    select distinct Race from tbl
    )
    , cbo as (
    select id, [year], Admission, Race 
    from id cross join yr cross join ad cross join rc
    
    )
    
    select cbo.id, cbo.[year], cbo.Admission, cbo.Race, count (*) as Record
    from cbo 
    left outer join tbl
      on cbo.id = tbl.ID
      and cbo.race = tbl.Race 
      and cbo.[year] = tbl.[year] 
      and cbo.Admission = tbl.Admission 
    group by cbo.id, cbo.[year], cbo.Admission, cbo.Race;


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, October 08, 2013 9:23 PM
  • If you have a large amount of data this is unlikely to perform well.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, October 08, 2013 9:23 PM
  • I think you don't understand my point, I ran your example which gives me # of records, I want something different. I want to have INSERT statement somehow.

    ANK HIT - if reply helps, please mark it as ANSWER or helpful post

    Wednesday, October 09, 2013 3:19 PM
  • I'm trying hard not to go Celko on you, but deserve it big time.

    Where do you want this inserted?  I have put a table name in this.

    with id as (
    select distinct id from tbl
    )
    , yr as (
    select distinct [year] from tbl
    )
    , ad as (
    select distinct Admission from tbl
    )
    , 
    , rc as (
    select distinct Race from tbl
    )
    , cbo as (
    select id, [year], Admission, Race 
    from id cross join yr cross join ad cross join rc
    
    )
    Insert into MyUnNamedTable(
    ID, [year], Admission, Race, Record
    
    )
    select cbo.id, cbo.[year], cbo.Admission, cbo.Race, count (*) as Record
    from cbo 
    left outer join tbl
      on cbo.id = tbl.ID
      and cbo.race = tbl.Race 
      and cbo.[year] = tbl.[year] 
      and cbo.Admission = tbl.Admission 
    group by cbo.id, cbo.[year], cbo.Admission, cbo.Race;


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, October 09, 2013 3:39 PM
  • Hi ANK,

    Please try the following codes:

    declare @tempTb table
    (
     ID int,
     [Year] int,
     ADMISSION char(1),
     RACE varchar(20),
     RECORD int
    )
    
    declare @Dic table
    (
     ID int,
     RACE varchar(20)
    )
    
    insert into @Dic (id,RACE) values(1,'AMERICAN'),(2,'ASIAN'),(3,'BLACK'),(4,'HISPANIC'),(5,'non-HISPANIC')
    
    insert into @tempTb (id,[Year],ADMISSION,RACE,RECORD) values
    (1       ,2008          ,'y'                 ,'AMERICAN'                 ,45),
    (2       ,2008          ,'y'                 ,'ASIAN'                 ,40),
    (3       ,2008          ,'y'                 ,'BLACK'                 ,4),
    (4       ,2008          ,'y'                 ,'HISPANIC'                 ,5),
    (1       ,2008          ,'x'                 ,'AMERICAN'                 ,45),
    (2       ,2008          ,'x'                 ,'ASIAN'                 ,40),
    (3       ,2008          ,'x'                 ,'BLACK'                 ,4);
    
    ;with CTE as(
    select distinct [year],ADMISSION 
    ,stuff((
      select ','''+RACE+''''
      from @tempTb t2
      where t1.[Year]=t2.[Year]
      and t1.ADMISSION = t2.ADMISSION
      for xml path('')),1,1,''
     ) as cols
    from @tempTb t1
    group by [year],ADMISSION
    ),cte2 as(
    select cte.[year],cte.ADMISSION,d.ID,d.RACE
    from cte 
    inner join @Dic as d on 1=1)
    insert into @tempTb (id,[Year],ADMISSION,RACE,RECORD) 
    select cte2.ID,cte2.[year],cte2.ADMISSION,cte2.RACE,0 from cte2
    left OUTER JOIN @tempTb t
    ON cte2.[year]=t.[Year]
    AND cte2.ADMISSION=t.ADMISSION
    AND cte2.ID =t.ID
    WHERE t.id IS NULL
    
    SELECT * FROM @tempTb
    order BY [year],ADMISSION;

    Best Regards,
    Allen Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 10, 2013 11:54 AM
    Moderator