USE CanvasWarehouse
GO

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

CREATE TABLE [dbo].[Canvas-course_score_fact-staging]
(
	score_id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, account_id bigint NOT NULL
	, course_id bigint NOT NULL
	, enrollment_id bigint NOT NULL
	, current_score float NULL
	, final_score float NULL
	, muted_current_score float NULL
	, muted_final_score float NULL
)
GO

BULK INSERT [dbo].[Canvas-course_score_fact-staging]
FROM
	'G:\Canvas\unpackedFiles\course_score_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-course_score_fact]', N'U') IS NOT NULL
BEGIN
	DROP TABLE [dbo].[Canvas-course_score_fact]
END
;

CREATE TABLE [dbo].[Canvas-course_score_fact]
(
	score_id bigint NOT NULL
	, canvas_id bigint NOT NULL
	, account_id bigint NOT NULL
	, course_id bigint NOT NULL
	, enrollment_id bigint NOT NULL
	, current_score float NULL
	, final_score float NULL
	, muted_current_score float NULL
	, muted_final_score float NULL
)
GO

INSERT INTO [dbo].[Canvas-course_score_fact]
SELECT
	score_id
	, canvas_id
	, account_id
	, course_id
	, enrollment_id
	, current_score
	, final_score
	, muted_current_score
	, muted_final_score
FROM
	[dbo].[Canvas-course_score_fact-staging]

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