USE CanvasWarehouse
GO

IF 
	OBJECT_ID(N'[dbo].[Canvas-enrollment_fact-staging]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-enrollment_fact-staging]
END
;

CREATE TABLE [dbo].[Canvas-enrollment_fact-staging]
(
	enrollment_id bigint NOT NULL
	, user_id bigint NOT NULL
	, course_id bigint NOT NULL
	, enrollment_term_id bigint NOT NULL
	, course_account_id bigint NOT NULL
	, course_section_id bigint NOT NULL
	, computed_final_score float NULL
	, computed_current_score float NULL
)
GO

BULK INSERT [dbo].[Canvas-enrollment_fact-staging]
FROM
	'G:\Canvas\unpackedFiles\enrollment_fact.txt'
WITH
	(
		FIRSTROW = 2
		, FIELDTERMINATOR = '\t'
		--, ROWTERMINATOR = '\r\n'
		-- This is due to unix encoding, the row terminator is not \n
		, ROWTERMINATOR = '0x0A'
		, KEEPNULLS
	)
GO

IF 
	OBJECT_ID(N'[dbo].[Canvas-enrollment_fact]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-enrollment_fact]
END
;

CREATE TABLE [dbo].[Canvas-enrollment_fact]
(
	enrollment_id bigint NOT NULL
	, user_id bigint NOT NULL
	, course_id bigint NOT NULL
	, enrollment_term_id bigint NOT NULL
	, course_account_id bigint NOT NULL
	, course_section_id bigint NOT NULL
--	, computed_final_score float NULL
--	, computed_current_score float NULL
)
GO

INSERT INTO [dbo].[Canvas-enrollment_fact]
SELECT
	enrollment_id
	, user_id
	, course_id
	, enrollment_term_id
	, course_account_id
	, course_section_id
--	, computed_final_score
--	, computed_current_score
FROM
	[dbo].[Canvas-enrollment_fact-staging]

IF 
	OBJECT_ID(N'[dbo].[Canvas-enrollment_fact-staging]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-enrollment_fact-staging]
END
;