I've come up with something similar, but using Jonathan's suggestion about the where clause.

USE [tempdb]
GO

DECLARE @DateFrom Date = '01 Aug 2018', @DateTo Date...

-- SQL 2012: Using Jeff Moden's string splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/

WITH CTE AS
(
SELECT F_PRODUCT, a.F_CAS_NUMBER, b.F_COMPONENT_ID
FROM dbo.PDF_MSDS
CROSS APPLY (SELECT Item FROM [dbo].[DelimitedSplit8K](F_CAS_NUMBERS,...

Not really sure what you're aiming for here.
Can you explain in a bit more detail.

How about

DECLARE @Increment Int = 0 -- or -1

SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
INTO #ChosenYear

What are you actually trying to do?  Are you moving views from one database to another?

Are you still trying to do this?
What is the purpose?  Do you want to move views from one database to another?

If you're using SQL 2012 you will need a string splitter. I've used Jeff Moden's - see http://www.sqlservercentral.com/articles/Tally+Table/72993/

For SQL 2016 onwards you can use STRING_SPLIT.

[code...

I can't recreate the error.  I created a database DATABASE_BI, ran the code & it was all fine.

The danger there is losing the use of indexes, especially in joins, & getting a big performance hit...

That's true - it is a bit scrappy.
Tidied up:

USE [tempdb];
GO

-- Test data:

IF OBJECT_ID('dbo.Data1') IS NOT NULL DROP TABLE dbo.Data1;

You can use a recursive query to get the levels for heirarchical data:

USE [tempdb]
GO

-- Test data:

DROP TABLE dbo.Data1;

You do not need a cursor for this.  You can just do the following

select @last_key_in_range = ISNULL(MAX(control_num) , -1)
from Tbl_Determination
where control_num>=CAST(@control_number_start as...

You could do this.... not sure if it's what you mean by "Total by Years".

USE [tempdb]
GO

-- Test data:

drop table...

That should work in SQL 2008.  What's the problem?