Sure. (Courtesy: J Livingston)

/* build (only Once) ============================================================================================================= */

IF EXISTS (

SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[fnTally]') AND xtype IN (N'FN', N'IF', N'TF')

)

DROP FUNCTION [dbo].[fnTally]

GO

CREATE FUNCTION [dbo].[fnTally]

/**********************************************************************************************************************

Purpose:

Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.

As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

Usage:

--===== Syntax example (Returns BIGINT)

SELECT t.N

FROM dbo.fnTally(@ZeroOrOne, @MaxN) t

;

Notes:

1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.

Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type

will cause the sequence to start at 1.

3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.

5. If @MaxN is negative or NULL, a "TOP" error will be returned.

6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger

number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with

that many values, you should consider using a different tool.

7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending

sort is required, use code similar to the following. Performance will decrease by about 27% but it's still

very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.

If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

DECLARE @MaxN BIGINT;

SELECT @MaxN = 1000;

SELECT DescendingN = @MaxN - N + 1

FROM dbo.fnTally(1,@MaxN);

8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

Revision History:

Rev 00 - Unknown - Jeff Moden

- Initial creation with error handling for @MaxN.

Rev 01 - 09 Feb 2013 - Jeff Moden

- Modified to start at 0 or 1.

Rev 02 - 16 May 2013 - Jeff Moden

- Removed error handling for @MaxN because of exceptional cases.

**********************************************************************************************************************/

(@ZeroOrOne BIT, @MaxN BIGINT)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN WITH

E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows

, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows

, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows

SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.

UNION ALL

SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN

;

GO

/*build (only Once) a table of years/weeks by using above Tally function with a sequential number to aid calculations later on*/

USE Master

GO

IF OBJECT_ID('Master..yearweek', 'U') IS NOT NULL DROP TABLE Master..yearweek ;

DECLARE @startyr as INT

DECLARE @no_yrs as INT

SET @startyr = 2010

SET @no_yrs = 20

SELECT

CAST(a.yearno + b.dayno as VARCHAR(6)) AS yrwk, CAST(a.yearno AS INT) AS yr, CAST(b.dayno AS INT) AS wk, ROW_NUMBER() OVER (ORDER BY a.yearno, b.dayno) AS rn

INTO yearweek

FROM

(SELECT CAST(@startyr + N AS VARCHAR) AS yearno FROM dbo.fnTally(0, @no_yrs))AS a

CROSS JOIN

(SELECT (CASE WHEN N < 10 THEN '0' + CAST(N AS VARCHAR) ELSE CAST(N AS VARCHAR) END ) AS dayno FROM dbo.fnTally(1, 52))AS b

/* UPDATE QUERY: once you have the files imported from SSIS into staging table some of the other columns can be updated*/

USE Master

GO

UPDATE staging

SET yr = yw.yr

, wk = yw.wk

, wkno = yw.rn

FROM staging

INNER JOIN yearweek AS yw

ON SUBSTRING(staging.Week, 3, 4) + SUBSTRING(staging.Week, 8, 2) = yw.yrwk

UPDATE staging

SET sCountry = PARSENAME(REPLACE(filename, '_', '.'), 3)

, sChar = PARSENAME(REPLACE(filename, '_', '.'), 2);