--I am not the author of this function, just modified it a bit to trim down the spaces before and after the delimiter
CREATE FUNCTION [dbo].[fnSplitText]
(
	@RowData varchar(4000),
	@SplitOn varchar(5)
)  
RETURNS @RtnValue table 
(
	Row int identity(1,1),
	Data varchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = ltrim(rtrim(Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

GO

CREATE TABLE #Articles (ArticleId int identity(1,1), ArticleText NVARCHAR(512))
CREATE TABLE #Categories (CategoryId int identity(1,1), CategoryText NVARCHAR(512))
CREATE TABLE #CategoriesInArticles (ArticleId INT, CategoryId INT) 
go
insert into #articles(ArticleText )
select 'Game ended 1-1'
union
select 'Book Review'

insert into #Categories(CategoryText)
select 'Sports'
union
select  'Entertainment'

insert into #CategoriesInArticles(articleid,categoryid)
select 1,1
union
select 2,2
go
declare @articletext nvarchar(600)
,@articlecategories nvarchar(600)

set @articleText = 'Book launched at sports event'
set @ArticleCategories = 'Entertainment,Business'

declare @articleid table(articleid int)
declare @categoryid table(categoryid int)

if not exists(select * from #articles where articletext=@articletext)
begin
	insert into #articles(articletext)
	output inserted.articleid into @articleid(articleid)
	select @articletext

	insert into #categories(categorytext)
	select data from dbo.fnsplitText(@articlecategories,',') fst
	where not exists(select categorytext from #categories 
		where categorytext=fst.data)

	insert into @categoryid(categoryid)
	select categoryid from #categories c
	join dbo.fnsplittext(@articlecategories,',') fst on c.categorytext=fst.data
	
	insert into #categoriesInArticles(articleid,categoryid)
	select a.articleid,c.categoryid
	from @articleID a cross join @categoryid c
end

select * from #articles
select * from #categories
select * from #categoriesinArticles