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

Question

User696604810 posted

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

User452040443 posted

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

Answer 2

User475983607 posted

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

Answer 3

User696604810 posted

thank you for reply

can you please help me doing that by while loop please

while 

until end iteration 

Answer 4

User1535942433 posted

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