USE [7001i] GO /****** Object: Table [dbo].[H60] Script Date: 21.1.2020 20.40.52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[H60]( [id] [int] IDENTITY(1,1) NOT NULL, [datetime] [datetime] NULL, [0002] [decimal](10, 4) NULL, [0003] [decimal](10, 4) NULL, [0004] [decimal](10, 4) NULL, [0005] [decimal](10, 4) NULL, [0006] [decimal](10, 4) NULL, [0007] [decimal](10, 4) NULL, [0008] [decimal](10, 4) NULL, [0009] [decimal](10, 4) NULL, [000A] [decimal](10, 4) NULL, [000B] [decimal](10, 4) NULL, [000C] [decimal](10, 4) NULL, [000E] [decimal](10, 4) NULL, [3104] [decimal](10, 4) NULL, [9108] [decimal](10, 4) NULL, [3108] [decimal](10, 4) NULL, [3109] [decimal](10, 4) NULL, [3110] [decimal](10, 4) NULL, [0107] [decimal](10, 4) NULL, [0111] [decimal](10, 4) NULL, [2201] [decimal](10, 4) NULL, [0203] [decimal](10, 4) NULL, [0205] [decimal](10, 4) NULL, [0206] [decimal](10, 4) NULL, [1A01] [bit] NULL, [1A02] [bit] NULL, [1A03] [bit] NULL, [1A04] [bit] NULL, [1A05] [bit] NULL, [1A06] [bit] NULL, [1A07] [bit] NULL, [1A0C] [bit] NULL, [1A19] [bit] NULL, [1A20] [bit] NULL, [AFF1] [decimal](10, 4) NULL, [AFF2] [decimal](10, 4) NULL, [1231] [decimal](10, 4) NULL, [2A91] [decimal](10, 4) NULL, [6C50] [decimal](10, 4) NULL, [5C51] [decimal](10, 4) NULL, [5C52] [decimal](10, 4) NULL, [5C53] [decimal](10, 4) NULL, [5C54] [decimal](10, 4) NULL, [5C55] [decimal](10, 4) NULL, [5C56] [decimal](10, 4) NULL, [5C57] [decimal](10, 4) NULL, [5C58] [decimal](10, 4) NULL, [5C59] [decimal](10, 4) NULL, CONSTRAINT [PK_H60] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[H60] ADD CONSTRAINT [DF_H60_datetime] DEFAULT (getdate()) FOR [datetime] GO /****** Object: StoredProcedure [dbo].[H60_data_to_sql] Script Date: 21.1.2020 20.40.52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[H60_data_to_sql] AS DECLARE @pump_data VARCHAR(max), @aika datetime SELECT @pump_data = BulkColumn FROM OPENROWSET(BULK 'C:/H60/alldata/alldata.json' , SINGLE_CLOB) JSON set @aika = getdate() IF (ISJSON(@pump_data) = 1) BEGIN PRINT'Json: OK'; INSERT INTO H60 SELECT GETdate(), * FROM OPENJSON(@pump_data, '$') WITH( [0002] decimal(10,4) '$."0002"', [0003] decimal(10,4) '$."0003"', [0004] decimal(10,4) '$."0004"', [0005] decimal(10,4) '$."0005"', [0006] decimal(10,4) '$."0006"', [0007] decimal(10,4) '$."0007"', [0008] decimal(10,4) '$."0008"', [0009] decimal(10,4) '$."0009"', [000A] decimal(10,4) '$."000A"', [000B] decimal(10,4) '$."000B"', [000C] decimal(10,4) '$."000C"', [000E] decimal(10,4) '$."000E"', [3104] decimal(10,4) '$."3104"', [9108] decimal(10,4) '$."9108"', [3108] decimal(10,4) '$."3108"', [3109] decimal(10,4) '$."3109"', [3110] decimal(10,4) '$."3110"', [0107] decimal(10,4) '$."0107"', [0111] decimal(10,4) '$."0111"', [2201] decimal(10,4) '$."2201"', [0203] decimal(10,4) '$."0203"', [0205] decimal(10,4) '$."0205"', [0206] decimal(10,4) '$."0206"', [1A01] BIT '$."1A01"', [1A02] BIT '$."1A02"', [1A03] BIT '$."1A03"', [1A04] BIT '$."1A04"', [1A05] BIT '$."1A05"', [1A06] BIT '$."1A06"', [1A07] BIT '$."1A07"', [1A0C] BIT '$."1A0C"', [1A19] BIT '$."1A19"', [1A20] BIT '$."1A20"', [AFF1] decimal(10,4) '$."AFF1"', [AFF2] decimal(10,4) '$."AFF2"', [1231] decimal(10,4) '$."1231"', [2A91] decimal(10,4) '$."2A91"', [6C50] decimal(10,4) '$."6C50"', [5C51] decimal(10,4) '$."5C51"', [5C52] decimal(10,4) '$."5C52"', [5C53] decimal(10,4) '$."5C53"', [5C54] decimal(10,4) '$."5C54"', [5C55] decimal(10,4) '$."5C55"', [5C56] decimal(10,4) '$."5C56"', [5C57] decimal(10,4) '$."5C57"', [5C58] decimal(10,4) '$."5C58"', [5C59] decimal(10,4) '$."5C59"' ); END ELSE BEGIN PRINT'Json: FAILED'; END GO