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

Question

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

Answer 1

If you need a WHILE loop for some reasons, then also consider a cursor. For example:

declare c cursor for  
    select d.BookdetailsId,  d.BookId,  d.PublishersPlaces,  d.[Status]  
    from #BookDetails d  
    inner join #mainbooks b on b.BookId = d.BookId  
    where  d.BookId in  
    (  
        select BookId  
        from #BookDetails  
        where [Status] is not null  
    )  
  
open c  
  
while 0=0  
begin  
  
    declare @bookDetailsId int  
    declare @Id int  
    declare @publisherPlaces nvarchar(50)  
    declare @status nvarchar(50)  
  
    fetch next from c into @bookDetailsId, @Id, @publisherPlaces, @status  
  
    if @@FETCH_STATUS <> 0 break  
  
    insert #BookHaveGeneralStatus   
    select @bookDetailsId, @Id, @publisherPlaces, @status  
      
end  
  
close c deallocate c  
  
select *   
from #BookHaveGeneralStatus   
order by BookgeneralId  

An INSERT without loop:

insert #BookHaveGeneralStatus  
select d.BookdetailsId, d.BookId, d.PublishersPlaces, d.[Status]  
from #BookDetails d  
inner join #mainbooks b on b.BookId = d.BookId  
where  d.BookId in  
(  
    select BookId  
    from #BookDetails  
    where [Status] is not null  
)  


  

Answer 2

Hi @ahmed salah ，

Please refer below statement and check whether it is helpful to you. Thanks.

;with cte as (  
    select bookid,count(*) count from #BookDetails  
	where Status is null  
	group by bookid)  
,cte1 as (  
    select bookid,count(*) count from #BookDetails  
	group by bookid  
)  
  
insert into  #BookHaveGeneralStatus   
  select  b.BookdetailsId,a.BookId,b.PublishersPlaces,b.Status   
  from #mainbooks a  
  inner join #BookDetails b  
  on a.BookId=b.BookId  
  where a.BookId  not in   
  (select a.BookId from cte a   
inner join cte1 b   
on a.BookId=b.BookId and a.count=b.count)  
  
select * from #BookHaveGeneralStatus  

Output:

BookgeneralId	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  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table