解析数据后在存储的proc中插入

debugcn 发表于 Dev

躲避球

我想寻求有关如何完全插入我使用select解析的解析数据的帮助。

这是我的代码专家

存储过程

USE [SMSGPS]
GO
/****** Object:  StoredProcedure [dbo].[ParsingProcedureR10V2]    Script Date: 11/12/2014 1:08:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ParsingProcedureR10V2]
@ProductCode VARCHAR(500)
AS
SET NOCOUNT ON;
SELECT [Account]  = LEFT(@ProductCode,CHARINDEX(',',@ProductCode) - 1),
   [date&time] = SUBSTRING(@ProductCode, 27, 12),
   [Status] = SUBSTRING(@ProductCode, 40, 1),
   [Lat] = SUBSTRING(@ProductCode, 42, 10),
   [Long] = SUBSTRING(@ProductCode, 53, 11),
   [Speed] = SUBSTRING(@ProductCode, 65, 4),
       [HDOP] = SUBSTRING(@ProductCode, 74, 3),
   [statcode] = SUBSTRING(@ProductCode, 78, 8),
   [ENGINE] = SUBSTRING(@ProductCode, 78, 1),
   [KM2] = SUBSTRING(@ProductCode, 123,11);

感谢所有提前帮助我的人，也感谢您允许我发帖的网站。

维拉

尝试以下操作：用表名替换TABLENAME并运行它。

USE [SMSGPS]
GO
/****** Object:  StoredProcedure [dbo].[ParsingProcedureR10V2]    Script Date: 11/12/2014 1:08:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ParsingProcedureR10V2]
@ProductCode VARCHAR(500)
AS
SET NOCOUNT ON; 

INSERT INTO TABLENAME (Account, date&time, Status, Lat, Long, Speed, HDOP,  
      statcode, ENGINE, KM2) 
SELECT [Account]  = LEFT(@ProductCode,CHARINDEX(',',@ProductCode) - 1),
   [date&time] = SUBSTRING(@ProductCode, 27, 12),
   [Status] = SUBSTRING(@ProductCode, 40, 1),
   [Lat] = SUBSTRING(@ProductCode, 42, 10),
   [Long] = SUBSTRING(@ProductCode, 53, 11),
   [Speed] = SUBSTRING(@ProductCode, 65, 4),
   [HDOP] = SUBSTRING(@ProductCode, 74, 3),
   [statcode] = SUBSTRING(@ProductCode, 78, 8),
   [ENGINE] = SUBSTRING(@ProductCode, 78, 1),
   [KM2] = SUBSTRING(@ProductCode, 123,11);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。