在Server 2008中将列分隔为逗号，行作为倾斜时存储在表中

Question

当前端以逗号分隔列和倾斜符号分隔行的形式发送数据时，如何使用Server 2008将数据存储在表中。

我只从堆栈溢出中得到逗号分隔的值。

现在我需要这个来处理多个列。

例如

从前端：

ProductId, Color~
1, red~
1, blue~
1, green~
2, null~
3, purple~
3, green

在后端:这些数据应该以表格格式存储。

Answer 1

USE [YourDB]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*Change name if needed*/
CREATE PROCEDURE uspInsertFromString 
@Str NVARCHAR(MAX)

WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @wrk_hr NVARCHAR(20)
    DECLARE @wrk_code NVARCHAR(20)
    DECLARE @active BIT

    DECLARE @IND INT
    DECLARE @StrRes NVARCHAR(MAX)
    DECLARE @StrRes2 NVARCHAR(MAX)

    SET @IND = CHARINDEX('~',@Str)

    WHILE(@IND > 0)
    BEGIN

        SET @StrRes = SUBSTRING(@Str, 1,  @IND-1)
        SET @StrRes2 = SUBSTRING(@StrRes, CHARINDEX(',', @Str)+1, LEN(@Str))

        SET @wrk_hr = CAST(SUBSTRING(@StrRes, 1, CHARINDEX(',', @Str)-1) AS VARCHAR(20))
        SET @wrk_code = CAST(SUBSTRING(@StrRes2, 1, CHARINDEX(',', @StrRes2)-1) AS VARCHAR(20))
        SET @active = CAST(SUBSTRING(@StrRes2, CHARINDEX(',', @StrRes2)+2, LEN(@Str)) AS BIT)

        INSERT INTO [dbo].[tbl_timesheet_entry] (wrk_hr, wrk_code, active ) VALUES (@wrk_hr, @wrk_code, @active) 

        SET @Str = SUBSTRING(@Str, @IND+1,  LEN(@Str))

        SET @IND = CHARINDEX('~',@Str)

    END

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Rollback any active or uncommittable transactions before
    -- inserting information in the ErrorLog
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

END CATCH;
END;

GO

用法：

EXEC uspInsertFromString '1, red, 1~1, blue, 0~1, green, 1~2, null, 0~3, purple, 1~3, green, 0~'