Can't Insert Book details using While Loop where status is not null?

Transact-SQL
,
1

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
2

You don't need a while loop. You can use a set based operation

 Select  * 
   from  #mainbooks m
	join  #BookDetails d
		on m.BookId = d.BookId
		and m.BookID in (select distinct BookID 
		                   from #BookDetails
						  where Status is not null)
3

thank you for reply I know that can done by query
but can you help me do it by While loop

4

https://www.sqlshack.com/sql-while-loop-with-simple-examples/

5

Ahmed .. any thoughts

6

What have you tried.

7

Ahmed,

why do you want to use a While Loop? It would be extra processing for no reason. Plus, a while loop will be slower.

8

OK thank you