ImportTimeEntries.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Garry Trinder
-- Create Date: 17/02/2019
-- Description: Truncates TimeEntry table and imports data from Azure Storage
-- =============================================
CREATE PROCEDURE ImportTimeEntries @Run bit
AS
BEGIN
	IF @Run <> 1
		BEGIN
			RAISERROR('RUN NOT TRUE', 16, 10)
		END
	
	BEGIN	
		TRUNCATE TABLE [TimeEntry]
	END

	BEGIN
		INSERT INTO 	[TimeEntry]
		SELECT		[TimeEntry].[EntryDate],
				[TimeEntry].[Employee],
				[TimeEntry].[Client],
				[TimeEntry].[Project],
				[TimeEntry].[Task],
				[TimeEntry].[Hours],
				[TimeEntry].[Notes]
		FROM OPENROWSET(BULK 'harvestdata/TimeEntries.json', DATA_SOURCE = 'harvestdata001', SINGLE_CLOB) as json
		CROSS APPLY OPENJSON(BulkColumn)
		WITH (	[EntryDate] date '$.spent_date',
			[Employee] varchar(25) '$.user.name',
			[Client] varchar(50) '$.client.name',
			[Project] varchar(50) '$.project.name',
			[Task] varchar(25) '$.task.name',
			[Hours] float '$.hours',
			[Notes] varchar(255) '$.notes'
		) as [TimeEntry]
	END

END
GO