November 30, 2020 at 1:08 pm
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
November 30, 2020 at 1:19 pm
Confused as to why a while loop, this is achievable in a set operation
Get the list of books which have at least one populated status, then use that to pull out the list and insert it into the table.
INSERT INTO #BookHaveGeneralStatus
SELECT * FROM #BookDetails
WHERE BookID IN (SELECT DISTINCT BookID FROM #BookDetails WHERE Status IS NOT NULL)
November 30, 2020 at 4:07 pm
thank you for reply
can you help me do it by while loop
November 30, 2020 at 5:41 pm
Why does it need to be a while loop?
SQL is a set based language and should be used as such. Row by row processing is slow hence the name ‘row by agonising row’.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply