How to Insert Book details using While Loop where status is not null?[Answered] RSS

• • Reply
  ahmedbarbary

  Member

  38 Points

  350 Posts

  How to Insert Book details using While Loop where status is not null?

  Nov 30, 2020 01:03 PM|ahmedbarbary|LINK

  I work on SQL SERVER 2012 . I face issue I can't use While loop to insert Books details that status is not NULL
  to table #BookHaveGeneralStatus .

  Meaning if any book have only one status not null on Books Details then insert it on table #BookHaveGeneralStatus using While loop .

  but if all Status is Null per Book Id for all rows on BookDetails Table then Not insert it on #BookHaveGeneralStatus .

   create table #mainbooks
   (
   BookId int,
   BookName nvarchar(50)
   )
   insert into #mainbooks
   values
   (1,'McGrawHill'),
   (2,'Visio'),
   (3,'Press'),
   (4,'ForDummies'),
   (5,'LearningIn21hours'),
   (6,'Udemy')
   ----DROP TABLE #BookDetails
   create table #BookDetails
   (
   BookdetailsId  int,
   BookId int,
   PublishersPlaces nvarchar(50),
   Status nvarchar(50)
   )
   ----select * from #BookDetails
   insert into #BookDetails
   values
   (94,1,'Cairo Jordan',NULL),
   (95,1,'tahrir sequare',NULL),
   (96,1,'ramsis sequare',NULL),
   (97,2,'Cairo Jordan',NULL),
   (98,2,'tahrir sequare',NULL),
   (99,2,'ramsis sequare',NULL),
   (100,3,'Cairo Jordan','Inshelf'),
   (101,3,'tahrir sequare','NULL'),
   (102,3,'ramsis sequare',NULL),
   (103,4,'Cairo Jordan','Inshelf'),
   (104,4,'tahrir sequare','Inprogress'),
   (105,4,'ramsis sequare','publish'),
   (106,5,'Cairo Jordan','Inshelf'),
   (107,5,'tahrir sequare','Inprogress'),
   (108,5,'ramsis sequare','publish'),
   (109,6,'Cairo Jordan','Inshelf'),
   (110,6,'tahrir sequare','Inprogress'),
   (111,6,'ramsis sequare','publish')
      
   create table #BookHaveGeneralStatus
   (
   BookgeneralId int,
   BookId int,
   PublishersPlaces nvarchar(50),
   Status nvarchar(50)
   )

  
  
  

  so that bookid 1 and 2 will not inserted

  and book id 3,4,5,6 will inserted

  so How to do that please using while loop ?

  while loop
  insert into table #BookHaveGeneralStatus values

  will be all data on table #BookDetails that have all status not null or at least one not null

  Book id 3 will added because it have at least on status not null

  
  expected result must added by while loop from #BookDetails to #BookHaveGeneralStatus

  BookdetailsId    BookId    PublishersPlaces    Status
   100    3    Cairo Jordan    Inshelf
   101    3    tahrir sequare    NULL
   102    3    ramsis sequare    NULL
   103    4    Cairo Jordan    Inshelf
   104    4    tahrir sequare    Inprogress
   105    4    ramsis sequare    publish
   106    5    Cairo Jordan    Inshelf
   107    5    tahrir sequare    Inprogress
   108    5    ramsis sequare    publish
   109    6    Cairo Jordan    Inshelf
   110    6    tahrir sequare    Inprogress
   111    6    ramsis sequare    publish

• • Reply
  imapsp

  Participant

  1120 Points

  291 Posts

  Re: How to Insert Book details using While Loop where status is not null?

  Nov 30, 2020 01:44 PM|imapsp|LINK

  Try:

  with CTE_Count as
  (
      select
          BookdetailsId,
          BookId,
          PublishersPlaces,
          Status,
          count(Status) over(partition by BookId) as QtyStatus
      from #BookDetails
  )
  
  insert into #BookHaveGeneralStatus
      select
          BookdetailsId,
          BookId,
          PublishersPlaces,
          Status
      from @CTE_Count
      where
          QtyStatus > 0

  Hope this help

  https://www.imoveisemexposicao.com.br/imoveis-venda-guarulhos-residencial-casa
• • Reply
  mgebhard

  All-Star

  52101 Points

  23237 Posts

  Re: How to Insert Book details using While Loop where status is not null?

  Nov 30, 2020 01:44 PM|mgebhard|LINK

  My best guess is you are asking how to write a SELECT with a WHERE and an INSERT.  

   INSERT INTO #BookHaveGeneralStatus
   SELECT * FROM #BookDetails WHERE Status IS NOT NULL

• • Reply
  ahmedbarbary

  Member

  38 Points

  350 Posts

  Re: How to Insert Book details using While Loop where status is not null?

  Nov 30, 2020 04:35 PM|ahmedbarbary|LINK

  thank you for reply

  can you please help me doing that by while loop please

  while 

  until end iteration 

• • Reply
  yij sun

  Contributor

  3390 Points

  1284 Posts

  Re: How to Insert Book details using While Loop where status is not null?

  Dec 01, 2020 08:30 AM|yij sun|LINK

  Hi ahmedbarbary,

  As far as I think,you could loop one time after select all data.Just like this:

  DECLARE @LoopCounter INT = 1
  WHILE(@LoopCounter <= 1)
  BEGIN
       INSERT INTO #BookHaveGeneralStatus
       SELECT * FROM #BookDetails as table1 where
       not EXISTS(
       SELECT BookId 
       FROM #BookDetails as table2
       where table1.BookId=table2.BookId
       GROUP BY BookId
       HAVING MAX(Status) IS NULL
       )
  SET @LoopCounter = @LoopCounter + 1
  END
  

  Result:

  

  Best regards,

  Yijing Sun

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.