CREATE TABLE [dbo].[dimDate](
	[dateKey] [int] NOT NULL,
	[fullDateAK] [date] NOT NULL,
	[dayNumberOfWeek] [tinyint] NOT NULL,
	[dayNumberOfMonth] [tinyint] NOT NULL,
	[dayNumberOfYear] [smallint] NOT NULL,
	[fullDayName] [nvarchar](10) NOT NULL,
	[weekNumberOfYear] [tinyint] NOT NULL,
	[monthNumberOfYear] [tinyint] NOT NULL,
	[fullMonthName] [nvarchar](10) NOT NULL,
	[trimesterNumber] [tinyint] NOT NULL,
	[yearNumber] [smallint] NOT NULL,
	[seasonName] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_DimDate_dateKey] PRIMARY KEY CLUSTERED 
(
	[dateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_DimDate_fullDateAK] UNIQUE NONCLUSTERED 
(
	[dateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SELECT DISTINCT
	dontKnowWhatToDoForThisColumn AS ????? 
	, CONVERT(Date, TestStartDate,101) AS FullDateAK 
	, DATEPART(dw, TestStartDate) AS dayNumberOfWeek
	, DATEPART(d, TestStartDate) AS dayNumberOfMonth
	, DATEPART(dy, TestStartDate) AS dayNumberOfYear
	, DATENAME(w, TestStartDate) AS fullDayName 
	, DATEPART(wk, TestStartDate) AS weekNumberOfYear 
	, DATEPART(m, TestStartDate) AS monthNumberOfYear 
	, DATENAME(m, TestStartDate) AS fullMonthName 
	, CASE
		WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 1 --first day of school + 12 weeks (trimester) back through mid year
		WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 3 --last day of school - 12 weeks (trimester) forward through to mid year
		ELSE 2
	  END AS trimesterNumber
	, YEAR(TestStartDate) AS yearNumber
	, CASE
		WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 'Fall' --first day of school + 12 weeks (trimester) back through mid year
		WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 'Spring' --last day of school - 12 weeks (trimester) forward through to mid year
		ELSE 'Winter'
	  END AS seasonName
FROM
	tblTestScores
ORDER BY CONVERT(Date, TestStartDate,101)

SELECT 
	ROW_NUMBER() OVER (ORDER BY TestStartDate) 
		AS dontKnowWhatToDoForThisColumn,
	*
FROM
(
	SELECT DISTINCT
		 CONVERT(Date, TestStartDate,101) AS FullDateAK 
		, DATEPART(dw, TestStartDate) AS dayNumberOfWeek
		, DATEPART(d, TestStartDate) AS dayNumberOfMonth
		, DATEPART(dy, TestStartDate) AS dayNumberOfYear
		, DATENAME(w, TestStartDate) AS fullDayName 
		, DATEPART(wk, TestStartDate) AS weekNumberOfYear 
		, DATEPART(m, TestStartDate) AS monthNumberOfYear 
		, DATENAME(m, TestStartDate) AS fullMonthName 
		, CASE
			WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 1 --first day of school + 12 weeks (trimester) back through mid year
			WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 3 --last day of school - 12 weeks (trimester) forward through to mid year
			ELSE 2
		  END AS trimesterNumber
		, YEAR(TestStartDate) AS yearNumber
		, CASE
			WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 'Fall' --first day of school + 12 weeks (trimester) back through mid year
			WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 'Spring' --last day of school - 12 weeks (trimester) forward through to mid year
			ELSE 'Winter'
		  END AS seasonName
	FROM
		tblTestScores
)s
ORDER BY CONVERT(Date, TestStartDate,101)

quote:

---

This code will start numbering at 1, you can offset it to whatever you like.

---

DECLARE @LastDateKey INT;
SELECT @LastDateKey = COALESCE(MAX(dateKey),0) FROM dimDate;

SELECT 
	ROW_NUMBER() OVER (ORDER BY TestStartDate) + @LastDateKey 
		AS dontKnowWhatToDoForThisColumn,
	*
FROM
(
......
......
etc

CREATE TABLE dbo.IdentityTest(id INT NOT NULL IDENTITY(1,1), x VARCHAR(32));

-- create a test data file with 3 rows
21,abcd
22,xyz
44,eee

BULK INSERT dbo.IdentityTest
   FROM 'f:\temp\Test.txt'
   WITH 
      (
         FIELDTERMINATOR =','
      );
SELECT * FROM dbo.IdentityTest
-- shows this:
1	abcd
2	xyz
3	eee
  
TRUNCATE TABLE  dbo.IdentityTest
BULK INSERT dbo.IdentityTest
FROM 'f:\temp\Test.txt'
WITH 
  (
     FIELDTERMINATOR =',',
     KEEPIDENTITY
  );
SELECT * FROM dbo.IdentityTest
-- shows this
21	abcd
22	xyz
44	eee

DROP TABLE dbo.IdentityTest;